Customer Placing The Largest Number Of Orders - Leetcode Solution

Problem Statement:

The problem statement on LeetCode is:

Write a SQL query to find the customer_id and customer_name who placed the largest number of orders in descending order.

The table schema is as follows:

Table: Orders

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_number   | int     |
| customer_id    | int     |
| order_date     | date    |
+----------------+---------+
order_number is the primary key for this table.
This table contains information about the orders placed.

Solution:

We need to find the customer_id and customer_name who placed the largest number of orders in descending order. For this, we will use the following steps:

1. We will count the number of orders for each customer_id in the Orders table.
2. We will join the Customers table with Orders table on customer_id.
3. We will select the customer_id, customer_name, and order_count columns.
4. We will sort the result in descending order of order_count.

We can write the following SQL query for this:

SELECT c.customer_id, c.customer_name, COUNT(o.order_number) AS order_count
FROM Customers c JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY order_count DESC
LIMIT 1;

In this query, we are using the JOIN operation to join the Customers and Orders tables on customer_id. The COUNT function is used to count the number of orders for each customer_id. We are grouping the result by customer_id and sorting it in descending order of order_count. We are then selecting the first row using the LIMIT 1 clause to get the customer who placed the largest number of orders.

Example:

Let's consider the following tables:

Customers Table:

+-------------+----------------+
| customer_id | customer_name  |
+-------------+----------------+
| 1           | John Doe       |
| 2           | Jane Smith     |
| 3           | David Johnson |
+-------------+----------------+

Orders Table:

+--------------+-------------+------------+
| order_number | customer_id | order_date |
+--------------+-------------+------------+
| 1            | 1           | 2021-01-01 |
| 2            | 2           | 2021-01-02 |
| 3            | 1           | 2021-01-03 |
| 4            | 2           | 2021-01-04 |
| 5            | 2           | 2021-01-05 |
| 6            | 3           | 2021-01-06 |
+--------------+-------------+------------+

On running the SQL query mentioned above, we will get the following result:

+-------------+---------------+-------------+
| customer_id | customer_name | order_count |
+-------------+---------------+-------------+
| 2           | Jane Smith    | 3           |
+-------------+---------------+-------------+

So, the customer with customer_id 2 i.e. Jane Smith has placed the largest number of orders which is 3.

1