LeetCode 586: Customer Placing the Largest Number of Orders

Problem Restatement

We are given an Orders table.

Each row represents one order and the customer who placed it.

We need to return the customer_number of the customer who placed the largest number of orders.

The test cases guarantee that exactly one customer has more orders than every other customer. The table has order_number as the primary key and customer_number as the customer ID.

Table

Orders

order_number is the primary key.

Example

Input:

Output:

Customer 3 placed two orders.

Customers 1 and 2 placed one order each.

So customer 3 has the largest number of orders.

First Thought: Count All Orders

A first attempt might be:

SELECT COUNT(*)
FROM Orders;

This only tells us the total number of orders.

But the problem asks which customer placed the most orders.

So we need to count orders per customer, not across the whole table.

Key Insight

Each order belongs to one customer.

So we can group rows by customer_number:

GROUP BY customer_number

Then each group represents all orders placed by one customer.

For each group, COUNT(*) gives the number of orders for that customer.

After that, sort customers by order count from largest to smallest and return the first row.

Algorithm

1. Read rows from Orders.
2. Group rows by customer_number.
3. Count the number of rows in each group.
4. Sort groups by the count in descending order.
5. Return the first customer_number.

Correctness

The query groups rows by customer_number, so every group contains exactly the orders placed by one customer.

For each group, COUNT(*) returns exactly the number of orders placed by that customer.

Sorting by COUNT(*) DESC places the customer with the largest number of orders first.

The problem guarantees that exactly one customer has the largest number of orders, so LIMIT 1 returns that customer.

Therefore, the query returns exactly the required customer_number.

Complexity

Let:

n = number of rows in Orders
c = number of distinct customers

The exact execution plan depends on the database engine.

Implementation

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;

Code Explanation

This selects the required output column:

SELECT customer_number

This reads order rows:

FROM Orders

This groups all orders from the same customer:

GROUP BY customer_number

This sorts customers by their number of orders:

ORDER BY COUNT(*) DESC

The customer with the most orders appears first.

This keeps only that customer:

LIMIT 1

Follow-Up: Return All Customers Tied for First

The original problem guarantees one winner, but the follow-up asks what to do if multiple customers share the largest order count.

We can compute each customer’s count, then compare it with the maximum count.

WITH order_counts AS (
    SELECT
        customer_number,
        COUNT(*) AS order_count
    FROM Orders
    GROUP BY customer_number
)
SELECT customer_number
FROM order_counts
WHERE order_count = (
    SELECT MAX(order_count)
    FROM order_counts
);

This returns every customer whose order count equals the maximum order count.

Testing

Sample data:

CREATE TABLE Orders (
    order_number INT PRIMARY KEY,
    customer_number INT
);

INSERT INTO Orders (order_number, customer_number) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 3);

Query:

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;

Expected result:

Additional test cases: