Customer Placing the Largest Number of Orders

Problem

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.

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.

Follow up: What if more than one customer has the largest number of orders, can you find all the customer_number in this case?

Solution

The problem Customer Placing the Largest Number of Orders can be solved using 2 different solutions. If it is guaranteed that there is exactly one customer who have placed more orders than any other customer, then a single subquery can be used to retrieve the customer number and the number of orders. It can then be ordered by the order count in descending order, which places the customer with the largest number of orders on top. If more than one customer has the largest number of orders, then the table needs to be grouped twice, once to retrieve the customer number and the number of orders and once again to find the largest number of orders.

Implementation (Basic)

# Write your MySQL query statement below

SELECT customer_number
FROM
  (
    SELECT customer_number, COUNT(order_number) AS order_count
    FROM orders
    GROUP BY customer_number
  ) AS T
ORDER BY order_count DESC
LIMIT 1;

Implementation (Follow up)

# Write your MySQL query statement below

SELECT customer_number
FROM orders
GROUP BY customer_number
HAVING
  COUNT(order_number) = (
    SELECT MAX(order_count)
    FROM (SELECT COUNT(order_number) AS order_count FROM orders GROUP BY customer_number) AS T
  );