# 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

| Column | Type | Meaning |
|---|---|---|
| `order_number` | int | Unique order ID |
| `customer_number` | int | Customer who placed the order |

`order_number` is the primary key.

## Example

Input:

| order_number | customer_number |
|---:|---:|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |

Output:

| customer_number |
|---:|
| 3 |

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:

```sql
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`:

```sql
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:

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

| Metric | Value | Why |
|---|---|---|
| Time | `O(n + c log c)` | Scan rows, group by customer, then sort customers |
| Space | `O(c)` | Store one aggregate count per customer |

The exact execution plan depends on the database engine.

## Implementation

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

## Code Explanation

This selects the required output column:

```sql
SELECT customer_number
```

This reads order rows:

```sql
FROM Orders
```

This groups all orders from the same customer:

```sql
GROUP BY customer_number
```

This sorts customers by their number of orders:

```sql
ORDER BY COUNT(*) DESC
```

The customer with the most orders appears first.

This keeps only that customer:

```sql
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.

```sql
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:

```sql
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:

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

Expected result:

| customer_number |
|---:|
| 3 |

Additional test cases:

| Case | Expected behavior |
|---|---|
| One customer has all orders | That customer is returned |
| Every customer has one order | Original problem avoids this tie case |
| Customer appears in many rows | Count all rows for that customer |
| Only one order exists | Return that order's customer |
| Follow-up has tied top customers | Return all tied customers with the CTE version |