586. Customer Placing the Largest Number of Orders


Query the customer_number from the orders table for the customer who has placed the largest number of orders.

It is guaranteed that exactly one customer will have placed more orders than any other customer.

The orders table is defined as follows:

| Column            | Type      |
|-------------------|-----------|
| order_number (PK) | int       |
| customer_number   | int       |
| order_date        | date      |
| required_date     | date      |
| shipped_date      | date      |
| status            | char(15)  |
| comment           | char(200) |

Sample Input

| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1            | 1               | 2017-04-09 | 2017-04-13    | 2017-04-12   | Closed |         |
| 2            | 2               | 2017-04-15 | 2017-04-20    | 2017-04-18   | Closed |         |
| 3            | 3               | 2017-04-16 | 2017-04-25    | 2017-04-20   | Closed |         |
| 4            | 3               | 2017-04-18 | 2017-04-28    | 2017-04-25   | Closed |         |

Sample 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'.

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


b'
\n
\n\n
\n

Solution

\n
\n

Approach: Using LIMIT [Accepted]

\n

Algorithm

\n

First, we can select the customer_number and the according count of orders using GROUP BY.

\n
SELECT\n    customer_number, COUNT(*)\nFROM\n    orders\nGROUP BY customer_number\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
customer_numberCOUNT(*)
11
21
32
\n

Then, the customer_number of first record is the result after sorting them by order count descending.

\n\n\n\n\n\n\n\n\n\n\n\n\n\n
customer_numberCOUNT(*)
32
\n

In MySQL, the LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. It takes one or two nonnegative numeric arguments, the first of which specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

\n

It can be used with only one argument, which specifies the number of rows to return from the beginning of the result set. So LIMIT 1 will return the first record.

\n

MySQL

\n
SELECT\n    customer_number\nFROM\n    orders\nGROUP BY customer_number\nORDER BY COUNT(*) DESC\nLIMIT 1\n;\n
\n
'