LeetCode 586 - Customer Placing the Largest Number of Orders

Database Language: SQL Server

Difficulty: Easy

Problem Description

Input

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.

Requirement

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.

Examples

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.

SQL Schema

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

TRUNCATE TABLE orders;
INSERT INTO orders (order_number, customer_number) values ('1', '1');
INSERT INTO orders (order_number, customer_number) values ('2', '2');
INSERT INTO orders (order_number, customer_number) values ('3', '3');
INSERT INTO orders (order_number, customer_number) values ('4', '3');

Solution

To find the customer who has placed the largest number of orders, the number of orders placed by each customer needs to be determined first. This can be determined by using the `COUNT()` aggregate function:

SELECT customer_number, COUNT(order_number) AS order_count
FROM Orders
GROUP BY customer_number

| customer_number | order_count |
| --------------- | ----------- |
| 1               | 1           |
| 2               | 1           |
| 3               | 2           |

Since the question wants the customer who placed the largest number of orders, the output of the previous query needs to be sorted by the `order_count` in descending order:

SELECT customer_number, COUNT(order_number) AS order_count
FROM Orders
GROUP BY customer_number
ORDER BY order_count DESC

| customer_number | order_count |
| --------------- | ----------- |
| 3               | 2           |
| 1               | 1           |
| 2               | 1           |

The query above returned all customers but the question only wants a single customer who has placed the most orders. To address the requirement, the output needs to be limited to just 1 row using the `LIMIT 1` clause:

SELECT TOP 1 customer_number, COUNT(order_number) AS order_count
FROM Orders
GROUP BY customer_number
ORDER BY order_count DESC

| customer_number | order_count |
| --------------- | ----------- |
| 3               | 2           |

Lastly, the output needed is just the `customer_number` without the number of orders made by that customer so the `order_count` column in the `SELECT` clause needs to be removed:

SELECT TOP 1 customer_number
FROM Orders
GROUP BY customer_number
ORDER BY order_count DESC

But this generates the following error because the `order_count` column is being referenced in the `ORDER BY` clause:

Query 1 ERROR: Msg: 207, Line 4, State: 1, Level: 16
Invalid column name 'order_count'.

To resolve this, the previous expression used to create the `order_count` column will be used in the `ORDER BY` clause:

# Final Solution Query
SELECT TOP 1 customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(order_number) DESC

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

Here's the query plan generated by SQL Server for this query:

  |--Sort(TOP 1, ORDER BY:([Expr1002] DESC))
   	|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
        	|--Stream Aggregate(GROUP BY:([leetcode].[dbo].[Orders].[customer_number]) DEFINE:([Expr1005]=Count(*)))
             	|--Sort(ORDER BY:([leetcode].[dbo].[Orders].[customer_number] ASC))
                  	|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Orders].[PK_Orders]))

And here's the fastest runtime for this query:

Related Articles: