The Most Frequently Ordered Products for Each Customer

The Most Frequently Ordered Products for Each Customer - Problem

Database Medium

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

Understanding the Visualization

Join Tables

Combine Orders and Products tables

Count Orders

Group by customer and product to count frequencies

Find Maximum

Use window function or CTE to identify top products

Key Takeaway

🎯 Key Insight: Use window functions to rank products by order frequency within each customer partition

Asked in

A Amazon 15 M Meta 12 G Google 8

Use window functions with RANK() OVER or CTEs with MAX() to identify products with the highest order frequency for each customer. Both approaches handle ties by including all products with the same maximum frequency.

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

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with RANK	O(n log n)	O(n)	Use RANK() to find products with highest order count per customer
Common Table Expression with MAX	O(n log n)	O(n)	Use CTE to find maximum order count, then filter products matching that count

Window Function with RANK — Algorithm Steps

Step 1: Join Orders and Products tables to get product information
Step 2: Group by customer_id and product_id to count order frequencies
Step 3: Use RANK() window function to rank products by frequency within each customer
Step 4: Filter to keep only the top-ranked products (rank = 1)

Visualization

Tap to expand

Step-by-Step Walkthrough

Count Orders

Group by customer and product to count frequencies

Rank Products

RANK() OVER partitioned by customer

Filter Top

Keep only rank = 1

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for window function ranking

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results and ranking

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with RANK — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler