Customer Placing the Largest Number of Orders - Solution & Explanation
Problem Statement
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?
Approach Overview
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.
Approach 1: Approach 1: Using SQL Group By and Max
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.
Complexity
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.
Approach 2: Approach 2: Manual Aggregation with Data Structures
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.
Code
Python
Complexity
Time Complexity: O(n), where n is the number of orders.
Space Complexity: O(m), where m is the number of unique customers.
Approach 3: Group By + Sorting
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.
Code
MySQL
Approach 4: Default Approach
Code
MySQL
Complexity Comparison
| 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 | — |
Detailed Complexity Analysis
| 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 |
Video Solution
LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 586 • Everyday Data Science • 23,103 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Customer Placing the Largest Number of Orders easy or hard?
Customer Placing the Largest Number of Orders Python/Java solution
How to solve Customer Placing the Largest Number of Orders in O(n)?
What is the best approach for Customer Placing the Largest Number of Orders?
Is Customer Placing the Largest Number of Orders asked at Google/Amazon/Meta?
What data structure is used in Customer Placing the Largest Number of Orders?
What is the time complexity of Customer Placing the Largest Number of Orders?
Ready to solve this problem?
Practice Customer Placing the Largest Number of Orders with our built-in code editor and test cases.
Practice on FleetCode