Skip to main content
Talentd

The Most Frequently Ordered Products for Each Customer - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon
Practice this problem

Problem Statement

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.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Aggregation + Correlated SubqueryO(n log n)O(n)Works in most SQL engines when window functions are unavailable
Group By + Window FunctionO(n log n)O(n)Best approach for "top item per group" queries in modern SQL systems

Video Solution

Leetcode 1596: The Most Frequently Ordered Products for Each Customer [SQL] • Frederik Müller • 6,254 views views

Watch 4 more video solutions →

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.

Ready to solve this problem?

Practice The Most Frequently Ordered Products for Each Customer with our built-in code editor and test cases.

Practice on FleetCode
Previous

The Most Recent Orders for Each Product