[SQL] [leetcode] 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 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.

The query 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.

---

풀이1 : WITH절 서브쿼리 이용

1. customer_number별 count를 가진 cnt with절 서브쿼리 생성

2. customer_number이 최대인 customer_number select

WITH cnt AS (
  SELECT customer_number, COUNT(customer_number) c
  FROM orders
  GROUP BY customer_number
)

SELECT customer_number
FROM cnt
WHERE c = (SELECT MAX(c) FROM cnt)

 

풀이2 : FROM절 서브쿼리 - DENSE_RANK 함수 이용 

1. FROM절 서브쿼리에서 customer_number별 customer_number과 order_number의 순위 select

2.  order_rank가 1인 customer_number select

SELECT customer_number
FROM (SELECT customer_number
    , DENSE_RANK() OVER(ORDER BY COUNT(order_number) DESC) order_rank
      FROM orders
      GROUP BY customer_number) r
WHERE order_rank = 1