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?
The key idea in #586 Customer Placing the Largest Number of Orders is to identify which customer appears most frequently in the orders table. Since each row represents an order placed by a customer, the problem can be solved using SQL aggregation.
A common approach is to group orders by customer_number and count how many orders each customer has placed using COUNT(). Once the counts are calculated, you can sort the results in descending order and select the customer with the highest count. Many SQL solutions use ORDER BY COUNT(*) DESC combined with LIMIT 1 to return the top result.
This method efficiently leverages SQL's built-in grouping and sorting capabilities without requiring complex joins or subqueries. Because the database engine handles aggregation internally, the solution remains concise and performant even for large datasets.
Time Complexity: O(n log n) due to sorting after aggregation. Space Complexity: O(k) where k is the number of unique customers stored during grouping.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| SQL GROUP BY with COUNT and ORDER BY | O(n log n) | O(k) |
Frederik Müller
Use these hints if you're stuck. Try solving on your own first.
MySQL uses a different expression to get the first records other than MSSQL's TOP expression.
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.
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.
1using System;
2using System.Data.SQLite;
3
4class Program {
5 static void Main() {
6 using (var connection = new SQLiteConnection("Data Source=:memory:")) {
7 connection.Open();
8 using (var command = new SQLiteCommand(connection)) {
9 // Assumes table and data exist in SQLite.
10 command.CommandText = @"
11 SELECT customer_number
12 FROM Orders
13 GROUP BY customer_number
ORDER BY COUNT(order_number) DESC
LIMIT 1;";
using (var reader = command.ExecuteReader()) {
if (reader.Read()) {
Console.WriteLine("Customer Number: " + reader.GetInt32(0));
}
}
}
}
}
}This C# solution utilizes SQLiteConnection and SQLiteCommand to execute a GROUP BY SQL query to find the customer with the highest number of orders. The results are read using a SQLiteDataReader.
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.
Time Complexity: O(n), where n is the number of orders.
Space Complexity: O(m), where m is the number of unique customers.
1def find_customer_with_most_orders(orders):
2 order_count = {}
3 # Count orders per customer
Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
Yes, similar database aggregation problems are common in technical interviews at large tech companies. They test your understanding of SQL grouping, counting, and result filtering.
GROUP BY allows you to collect rows with the same customer_number and treat them as a single group. This makes it possible to count how many orders each customer placed and compare those totals.
The optimal approach is to group records by customer_number and count how many orders each customer placed. After aggregation, sort the results by the order count in descending order and return the top customer. This leverages SQL's GROUP BY and COUNT functions efficiently.
The solution primarily relies on SQL aggregation using GROUP BY along with the COUNT() function. Sorting with ORDER BY helps identify the customer with the maximum number of 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.