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?
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.
C++
Java
Python
C#
JavaScript
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.
Time Complexity: O(n), where n is the number of orders.
Space Complexity: O(m), where m is the number of unique customers.
| 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. |
LeetCode 586: Customer Placing the Largest Number of Orders [SQL] • Frederik Müller • 5,289 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