[LeetCode][MySQL] 586. Customer Placing the Largest Number of Orders

Easy 🔗586. Customer Placing the Largest Number of Orders

📝문제 요약

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.

The result format is in the following example.

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 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 has the largest number of orders, can you find all the customer_number in this case?

✏️문제 풀이

SELECT

SELECT customer_number

• customer_number : 가장 주문을 많이 한 고객의 번호

FROM

FROM (
    SELECT customer_number, COUNT(*) AS order_count
    FROM Orders
    GROUP BY 1
) AS customer_counts

• 서브쿼리(customer_counts)
  • Orders 테이블에서 customer_number별로 묶어서(GROUP BY) 고객당 주문 수(COUNT(*))를 셈
  • GROUP BY 1은 첫 번째 컬럼, 즉 customer_number 기준 그룹화

ORDER BY

ORDER BY order_count DESC

• order_count(주문 수)가 가장 많은 순서로 내림차순 정렬

LIMIT

LIMIT 1

• 정렬된 결과에서 1번째 데이터만 출력. 즉 , 가장 많은 주문수의 데이터

💯제출 코드

SELECT customer_number
FROM (
    SELECT customer_number, COUNT(*) AS order_count
    FROM Orders
    GROUP BY 1
) AS customer_counts
ORDER BY order_count DESC
LIMIT 1