Customer Placing the Largest Number of Orders - Problem

You are given a table Orders that contains information about orders and their corresponding customers.

Write a SQL 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.

Table Schema

Orders
Column Name Type Description
order_number PK int Primary key - unique identifier for each order
customer_number int Customer identifier who placed the order
Primary Key: order_number
Note: Each row represents one order placed by a customer

Input & Output

Example 1 — Basic Case
Input Table:
order_number customer_number
1 1
2 2
3 3
4 3
Output:
customer_number
3
💡 Note:

Customer 3 has placed 2 orders (order_number 3 and 4), while customers 1 and 2 have each placed only 1 order. Therefore, customer 3 is returned as the customer with the largest number of orders.
Example 2 — Single Customer Dominant
Input Table:
order_number customer_number
1 5
2 5
3 5
4 1
5 2
Output:
customer_number
5
💡 Note:

Customer 5 has placed 3 orders, which is more than any other customer. Customer 1 and 2 have each placed only 1 order, making customer 5 the clear winner.

Constraints

  • 1 ≤ order_number ≤ 500
  • 1 ≤ customer_number ≤ 500
  • All order_number are unique
  • Exactly one customer will have the most orders

Visualization

Tap to expand
Customer Placing the Largest Number of Orders INPUT Orders Table order_number customer_number 1 1 2 2 3 3 4 3 5 3 Customer Order Counts: Cust 1: 1 order Cust 2: 1 order Cust 3: 3 orders Find: MAX orders customer ALGORITHM STEPS 1 GROUP BY Group orders by customer_number 2 COUNT(*) Count orders per customer 3 ORDER BY DESC Sort by count descending 4 LIMIT 1 Take first (largest) result SELECT customer_number FROM Orders GROUP BY customer_number ORDER BY COUNT(*) DESC LIMIT 1 FINAL RESULT customer_number 3 Customer 3 Wins! Order Count Comparison C1 1 C2 1 C3 3 OK - Max Orders Found Output: {"customer_number": 3} Key Insight: GROUP BY with COUNT(*) aggregates orders per customer. ORDER BY DESC + LIMIT 1 efficiently finds the maximum without subqueries. Time: O(n log n), Space: O(k) where k = unique customers. TutorialsPoint - Customer Placing the Largest Number of Orders | Optimal Solution

Related Problems

Asked in
Amazon 15 Facebook 12 Google 8
23.5K Views
High Frequency
~8 min Avg. Time
891 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen