Customer Placing the Largest Number of Orders | LeetCode | MSSQL
Tags: leetcode, mssql, beginners, codenewbie
Published: 2023-05-14 15:10:02

## The Problem

In a database, we have a table `Orders` which contains information about the order ID and the customer ID as described below:

Table: Orders

| Column Name     | Type |
|-----------------|------|
| order_number    | int  |
| customer_number | int  |

In this table, `order_number` is the primary key. Our task is to write an SQL query to find the `customer_number` for the customer who has placed the largest number of orders. It's guaranteed that exactly one customer will have placed more orders than any other customer.

The query result format should be as follows:

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 more than either customer 1 or 2 (each of whom only has one order). Therefore, the result is `customer_number` 3.

## The Solution

Let's explore two different SQL queries to solve this problem. We'll highlight their main characteristics, strengths, and potential drawbacks.

### Source Code 1

The first query utilizes Common Table Expressions (CTEs) to simplify the complex SQL operations:

```sql
WITH CustomerOrders AS (
    SELECT customer_number, COUNT(order_number) as num_orders
    FROM Orders
    GROUP BY customer_number
), RankedCustomers AS (
    SELECT customer_number, num_orders,
           RANK() OVER (ORDER BY num_orders DESC) as rank
    FROM CustomerOrders
)
SELECT customer_number
FROM RankedCustomers
WHERE rank = 1;
```

This query first generates a table `CustomerOrders` with each customer and their total number of orders. Then, it ranks these customers by their order count in a descending order in the `RankedCustomers` table. Finally, it selects the customer with the highest rank, i.e., the one with the most orders.

This method is flexible and scalable, making it suitable for more complex scenarios. However, it may not be as efficient as other methods for simple queries due to the overhead of creating CTEs.

![source code 1 result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hkdoh1znezdnfhq9e3lj.png)

### Source Code 2

The second query is a simpler and more direct solution:

```sql
SELECT TOP 1 customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(order_number) DESC;
```

This query groups the `Orders` table by `customer_number`, counts the number of orders for each customer, and sorts the customers in descending order of their order count. It then selects the first customer, which is the one with the most orders.

This method is more straightforward and efficient than the first one, especially for simple queries like this. However, it might not be as flexible or adaptable for more complex scenarios.

![source code 2 result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/99snzi5ezmgy446tberk.png)

Remember, the choice of method often depends on the specific requirements and constraints of your problem.

You can find the original problem at [LeetCode](https://leetcode.com/problems/customer-placing-the-largest-number-of-orders/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}