Table: Orders
+-----------------+----------+ | Column Name | Type | +-----------------+----------+ | order_number | int | | customer_number | int | +-----------------+----------+ order_number is the primary key (column with unique values) for this table. This table contains information about the order ID and the customer ID.
Write a solution to find the customer_number for the customer who has placed the largest number of orders.
The test cases are generated so that exactly one customer will have placed more orders than any other customer.
The result format is in the following example.
Example 1:
Input: Orders table: +--------------+-----------------+ | order_number | customer_number | +--------------+-----------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 3 | +--------------+-----------------+ Output: +-----------------+ | customer_number | +-----------------+ | 3 | +-----------------+ Explanation: The customer with number 3 has two orders, which is greater than either customer 1 or 2 because each of them only has one order. So the result is customer_number 3.
Follow up: What if more than one customer has the largest number of orders, can you find all the customer_number in this case?
Problem Overview: The Orders table stores customer purchases with a customer_number. The task is to return the customer who placed the highest number of orders. If one customer appears most frequently, that customer’s ID should be returned.
Approach 1: SQL GROUP BY with MAX Aggregation (Time: O(n), Space: O(k))
This approach relies on SQL aggregation. First group all rows by customer_number using GROUP BY. Then compute the order count for each customer with COUNT(*). The customer with the maximum count can be retrieved using a subquery with MAX or by sorting the grouped results and selecting the top record.
The key insight: aggregation compresses repeated rows into frequency counts directly inside the database engine. Instead of scanning repeatedly, the database builds grouped buckets for each customer. The final step simply returns the customer whose count equals the maximum value.
This is the most natural solution for a database problem because relational systems are optimized for grouping and aggregation operations. Complexity is linear relative to the number of rows O(n), while space is O(k) where k is the number of unique customers.
Approach 2: Manual Aggregation with Data Structures (Time: O(n), Space: O(k))
If the data is processed outside the database (for example in Python or Java), you can simulate SQL aggregation using a hash map. Iterate through every order and maintain a frequency counter where the key is customer_number and the value is the number of orders placed by that customer.
During the iteration, update the count for each customer and track the maximum frequency seen so far. After processing all rows, return the customer associated with the highest count.
This approach mirrors SQL GROUP BY behavior but uses a hash-based frequency table. Each lookup and update is constant time on average, so the full scan runs in O(n) time. Memory usage grows with the number of distinct customers, resulting in O(k) space. This technique is common in hash table problems and is useful when processing query results in application code rather than directly in SQL.
Recommended for interviews: The SQL GROUP BY approach is the expected answer because the problem is designed around database querying. Interviewers want to see correct use of aggregation functions and filtering with MAX. Understanding the manual hash-map version still matters since it shows you understand how grouping works internally rather than relying only on SQL syntax.
This approach leverages the SQL capabilities of grouping and aggregation to solve the problem efficiently.
First, we perform a GROUP BY operation on the customer_number to count the number of orders per customer. Then, we use ORDER BY in descending order and LIMIT 1 to retrieve only the customer with the maximum count of orders.
This C code uses sqlite3 to execute a SQL query to find the customer with the largest number of orders. It prepares a SQL statement, executes it, and iterates through the result to print the customer number with the most orders.
Time Complexity: O(n), where n is the number of orders.
Space Complexity: O(1), as we are not using any additional space outside of the database's space.
This approach involves manually iterating over the orders and using a data structure like a hash map (or dictionary) to count the number of orders per customer. Once counted, we can iterate over this collection to find the customer with the maximum orders.
This Python code manually counts customer orders using a dictionary to map customer numbers to their respective order counts. Finally, it identifies the customer with the maximum count.
Python
Time Complexity: O(n), where n is the number of orders.
Space Complexity: O(m), where m is the number of unique customers.
We can use GROUP BY to group the data by customer_number, and then sort the groups in descending order by count(1). Finally, we can take the customer_number of the first record as the result.
MySQL
MySQL
| Approach | Complexity |
|---|---|
| Approach 1: Using SQL Group By and Max | Time Complexity: O(n), where n is the number of orders. |
| Approach 2: Manual Aggregation with Data Structures | Time Complexity: O(n), where n is the number of orders. |
| Group By + Sorting | — |
| Default Approach | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL GROUP BY with MAX | O(n) | O(k) | Best for database queries where aggregation can be handled directly in SQL |
| Manual Hash Map Aggregation | O(n) | O(k) | Useful when processing rows in application code instead of SQL |
LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 586 • Everyday Data Science • 21,664 views views
Watch 9 more video solutions →Practice Customer Placing the Largest Number of Orders with our built-in code editor and test cases.
Practice on FleetCode