The Most Frequently Ordered Products for Each Customer - Problem

Given three tables Customers, Orders, and Products, write a SQL query to find the most frequently ordered product(s) for each customer.

Requirements:

  • Find the product(s) that each customer has ordered most frequently
  • Include both product_id and product_name in the result
  • Only include customers who have placed at least one order
  • If a customer has multiple products with the same highest frequency, include all of them

Table Schemas:

Customers table contains customer information with unique customer_id.

Orders table contains order details. Note that no customer will order the same product more than once in a single day.

Products table contains product information with unique product_id.

Table Schema

Customers
Column Name Type Description
customer_id PK int Unique customer identifier
name varchar Customer name
Primary Key: customer_id
Orders
Column Name Type Description
order_id PK int Unique order identifier
order_date date Date when order was placed
customer_id int Foreign key to Customers table
product_id int Foreign key to Products table
Primary Key: order_id
Products
Column Name Type Description
product_id PK int Unique product identifier
product_name varchar Name of the product
price int Price of the product
Primary Key: product_id

Input & Output

Example 1 — Multiple Orders per Customer
Input Tables:
Customers
customer_id name
1 Alice
2 Bob
3 Tom
Orders
order_id order_date customer_id product_id
1 2020-07-31 1 1
2 2020-07-30 2 2
3 2020-07-31 1 2
4 2020-07-29 3 3
5 2020-07-28 3 3
6 2020-07-27 1 2
7 2020-07-26 2 1
8 2020-07-25 2 1
Products
product_id product_name price
1 keyboard 120
2 mouse 80
3 screen 600
Output:
customer_id product_id product_name
1 2 mouse
2 1 keyboard
3 3 screen
💡 Note:

Customer 1 ordered product 2 (mouse) twice and product 1 (keyboard) once, so mouse is most frequent. Customer 2 ordered product 1 (keyboard) twice and product 2 (mouse) once, so keyboard is most frequent. Customer 3 ordered product 3 (screen) twice, making it the most frequent.
Example 2 — Tied Products
Input Tables:
Customers
customer_id name
1 Alice
Orders
order_id order_date customer_id product_id
1 2020-07-31 1 1
2 2020-07-30 1 2
3 2020-07-29 1 1
4 2020-07-28 1 2
Products
product_id product_name price
1 keyboard 120
2 mouse 80
Output:
customer_id product_id product_name
1 1 keyboard
1 2 mouse
💡 Note:

Customer 1 ordered both keyboard and mouse exactly 2 times each. Since both products have the same highest frequency, both are included in the result.
Example 3 — Single Orders
Input Tables:
Customers
customer_id name
1 Alice
2 Bob
Orders
order_id order_date customer_id product_id
1 2020-07-31 1 1
2 2020-07-30 2 2
Products
product_id product_name price
1 keyboard 120
2 mouse 80
Output:
customer_id product_id product_name
1 1 keyboard
2 2 mouse
💡 Note:

Each customer ordered only one product once, so those single products are their most frequently ordered items.

Constraints

  • 1 ≤ customer_id ≤ 1000
  • 1 ≤ order_id ≤ 10000
  • 1 ≤ product_id ≤ 1000
  • No customer will order the same product more than once in a single day
  • All customers in the result must have placed at least one order

Visualization

Tap to expand
Most Frequently Ordered Products per Customer INPUT: Three Tables Customers customer_id name 1 Alice 2 Bob Orders order_id cust_id prod_id 1 1 101 2 1 101 3 1 102 Products product_id product_name 101 Laptop 102 Phone Alice: Laptop x2, Phone x1 Bob: ordered products... Find most frequent per customer ALGORITHM STEPS 1 Count Order Frequency GROUP BY customer_id, product_id with COUNT(*) 2 Find Max per Customer Use window function RANK() or MAX() OVER 3 Filter Top Products WHERE count = max_count Handles ties automatically 4 JOIN Product Names Join with Products table for product_name WITH counts AS ( SELECT customer_id, product_id, COUNT(*) RANK() OVER(...) ) SELECT * WHERE rk = 1 FINAL RESULT Output Table cust_id prod_id name 1 101 Laptop 2 102 Phone 2 103 Mouse 3 101 Laptop ... (all customers with orders) Result Includes: [OK] customer_id, product_id [OK] product_name from JOIN [OK] Ties handled (cust 2) Complexity Time: O(n log n) - sorting/ranking Space: O(n) - window function Key Insight: Use RANK() or DENSE_RANK() window function to handle ties efficiently. Group orders by customer and product, count frequencies, then rank within each customer partition. Filter where rank = 1 to get all products with maximum frequency. This elegantly handles multiple products with same count. TutorialsPoint - The Most Frequently Ordered Products for Each Customer | Optimal Solution

Related Problems

Asked in
Amazon 15 Meta 12 Google 8
28.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen