The Most Frequently Ordered Products for Each Customer solution and explanation
4 more videos on this problem
Leetcode 1596: The Most Frequently Ordered Products for Each Customer [SQL]Frederik Müller · 10:43 · 6K views
LeetCode Medium 1596 Amazon Interview SQL Question with Detailed ExplanationEveryday Data Science · 16:38 · 5K views
Multiple Common Table Expressions (CTEs) Explained! Advanced SQL Data Science - Leetcode 1596Everyday Data Science · 9:05 · 802 views
LeetCode 1549, 1596, 1341, 1613 (Zoom SQL Study Group)adigolee · 34:58 · 285 views
SQL Tutorials - The Most Frequently Ordered Products for Each Customer - Part 24Ghumman Tech · 7:36 · 39 views
The problem
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.
Approach overview
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.
Solution
We group the Orders table by customer_id and product_id, and then use the window function rank(), which assigns a rank to each product_id in each customer_id group based on its frequency in descending order. Finally, we select the product_id with a rank of 1 for each customer_id, which is the most frequently ordered product for that customer_id.
# Write your MySQL query statement below
WITH
T AS (
SELECT
customer_id,
product_id,
RANK() OVER (
PARTITION BY customer_id
ORDER BY COUNT(1) DESC
) AS rk
FROM Orders
GROUP BY 1, 2
)
SELECT customer_id, product_id, product_name
FROM
T
JOIN Products USING (product_id)
WHERE rk = 1;Complexity analysis
| 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 |
Frequently asked questions
Is The Most Frequently Ordered Products for Each Customer easy or hard?
The problem is rated Medium because it requires understanding SQL aggregation and window functions. Developers familiar with GROUP BY but new to ranking functions may find it challenging, while those experienced with analytics queries will recognize the "top per group" pattern quickly.
The Most Frequently Ordered Products for Each Customer Python/Java solution
This problem is designed for SQL rather than general-purpose languages like Python or Java. The logic relies on database operations such as GROUP BY and window functions, typically written as a single SQL query in systems like MySQL or PostgreSQL.
How to solve The Most Frequently Ordered Products for Each Customer in O(n)?
Pure O(n) complexity is uncommon in SQL engines because grouping and ranking usually require sorting. The practical solution aggregates order counts with GROUP BY and then ranks them using a window function. This produces efficient performance close to O(n log n) in most databases.
What is the best approach for The Most Frequently Ordered Products for Each Customer?
The best approach uses GROUP BY combined with a window function like DENSE_RANK or RANK. First count how many times each (customer_id, product_id) pair appears. Then rank products within each customer partition by order frequency and return those with rank = 1. This method is concise and avoids repeated subqueries.
Is The Most Frequently Ordered Products for Each Customer asked at Google/Amazon/Meta?
SQL problems involving top items per group are common in data engineering and analytics interviews at companies like Amazon, Google, and Meta. Variants of this problem test your ability to use GROUP BY, window functions, and ranking logic to analyze relational datasets.
What data structure is used in The Most Frequently Ordered Products for Each Customer?
The solution relies on relational database aggregation rather than traditional in-memory data structures. Internally, the database uses grouping and sorting mechanisms to compute counts and apply window ranking functions such as DENSE_RANK.
What is the time complexity of The Most Frequently Ordered Products for Each Customer?
The typical SQL solution runs in about O(n log n) time due to grouping and sorting operations required for aggregation and window ranking. Space complexity is O(n) because the query must store grouped counts for each customer-product pair before ranking.