Watch 4 video solutions for The Most Frequently Ordered Products for Each Customer, a medium level problem involving Database. This walkthrough by Frederik Müller has 6,213 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id is the column with unique values for this table. This table contains information about the customers.
Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | product_id | int | +---------------+---------+ order_id is the column with unique values for this table. This table contains information about the orders made by customer_id. No customer will order the same product more than once in a single day.
Table: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | price | int | +---------------+---------+ product_id is the column with unique values for this table. This table contains information about the products.
Write a solution to find the most frequently ordered product(s) for each customer.
The result table should have the product_id and product_name for each customer_id who ordered at least one order.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Customers table: +-------------+-------+ | customer_id | name | +-------------+-------+ | 1 | Alice | | 2 | Bob | | 3 | Tom | | 4 | Jerry | | 5 | John | +-------------+-------+ Orders table: +----------+------------+-------------+------------+ | order_id | order_date | customer_id | product_id | +----------+------------+-------------+------------+ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 3 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------------+ Products table: +------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | +------------+--------------+-------+ Output: +-------------+------------+--------------+ | customer_id | product_id | product_name | +-------------+------------+--------------+ | 1 | 2 | mouse | | 2 | 1 | keyboard | | 2 | 2 | mouse | | 2 | 3 | screen | | 3 | 3 | screen | | 4 | 1 | keyboard | +-------------+------------+--------------+ Explanation: Alice (customer 1) ordered the mouse three times and the keyboard one time, so the mouse is the most frequently ordered product for them. Bob (customer 2) ordered the keyboard, the mouse, and the screen one time, so those are the most frequently ordered products for them. Tom (customer 3) only ordered the screen (two times), so that is the most frequently ordered product for them. Jerry (customer 4) only ordered the keyboard (one time), so that is the most frequently ordered product for them. John (customer 5) did not order anything, so we do not include them in the result table.
Problem Overview: You have order records containing customers and products. The task is to return the product each customer orders the most frequently. If multiple products tie for the highest frequency, return all of them.
Approach 1: Aggregation with Correlated Subquery (O(n log n) time, O(n) space)
The straightforward way is to count how many times each (customer_id, product_id) pair appears using GROUP BY. After computing the counts, a correlated subquery compares each product's order count with the maximum count for that customer. Only rows whose count equals that maximum are returned. This approach works in most SQL engines but executes a nested lookup for each grouped row, which can become expensive as the dataset grows.
Approach 2: Group By + Window Function (O(n log n) time, O(n) space)
This is the clean and scalable approach. First, aggregate orders with GROUP BY customer_id, product_id to compute the number of times each product was ordered. Then apply a window ranking function such as DENSE_RANK() or RANK() partitioned by customer_id and ordered by the frequency in descending order. The window function assigns rank 1 to the most frequently ordered products per customer. Finally, filter rows where the rank equals 1. Window functions avoid repeated scans and express the logic clearly, which is why they are commonly used in analytics queries.
This pattern appears frequently in SQL interview questions involving SQL analytics. The combination of aggregation and ranking is a core technique in database querying, especially when extracting "top N per group" results. Window functions such as RANK, DENSE_RANK, and ROW_NUMBER are key tools for these scenarios and are widely supported in modern systems like MySQL, PostgreSQL, and SQL Server.
Recommended for interviews: The Group By + Window Function approach. Interviewers expect you to recognize the "top item per group" pattern and solve it with a window function. Knowing the correlated subquery approach shows foundational SQL knowledge, but using ranking functions demonstrates stronger query design and familiarity with modern SQL features.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + Correlated Subquery | O(n log n) | O(n) | Works in most SQL engines when window functions are unavailable |
| Group By + Window Function | O(n log n) | O(n) | Best approach for "top item per group" queries in modern SQL systems |
