586 Customer Placing the Largest Number of Orders

1. Description

Table: Orders

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
order_number is the primary key for this table.
This table contains information about the order ID and the customer ID.

 

Write an SQL query 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.

 

2. Algorithms

1. Group by customer_number.
2. Find the customer_number with maximum number of orders.

 

3. Codes

/* Query1 */ 
# Write your MySQL query statement below
SELECT customer_number 
  FROM orders 
 GROUP BY customer_number 
HAVING COUNT(*) = (SELECT MAX(num_orders)
                     FROM (SELECT customer_number, COUNT(*) AS num_orders
                             FROM orders 
                            GROUP BY customer_number) AS order_max)
                            
/* Query2 */ 
SELECT customer_number
  FROM order
 GROUP BY customer_number
 ORDER BY COUNT(*) DESC
 LIMIT 1;

 

4. Conclusion