[Leetcode/SQL] 601. Human Traffic of Stadium

문제는 여기서 볼 수 있습니다.

 

Table: Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date is the primary key for this table.
Each row of this table contains the visit date and visit id to the stadium with the number of people during the visit.
No two rows will have the same visit_date, and as the id increases, the dates increase as well.

3행이상의 연속된 record에 대해서 people이 100명 이상일 경우, 해당 record들을 visit_date에 대한 오름차순으로 정렬하라.

 

Example 1:

Input: 
Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
Output: 
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
Explanation: 
The four rows with ids 5, 6, 7, and 8 have consecutive ids and each of them has >= 100 people attended. Note that row 8 was included even though the visit_date was not the next day after row 7.
The rows with ids 2 and 3 are not included because we need at least three consecutive ids.

 

 

Code:

with t as (select id, visit_date, people, id - row_number() over(order by id) as section
           from stadium
           where people >= 100 
          )

select t.id, t.visit_date ,t.people
from t
where section in (select section from t group by section having count(*) >=3);

/*
예시에 대한 id - row_number() over(order by id) 실행과정
2-1 = 1
3-2 = 1
5-3 = 2
6-4 = 2
7-5 = 2
8-6 = 2
*/

- with구문 : people이 100 이상인 row만 남기면서 연속된 row일 경우 같이 grouping 되도록 section 컬럼을 정의합니다.

- where ~ in 구문 : section별로 grouping 시, 3개 이상의 row를 가질 때 문제의 요구사항에 부합함을 체크합니다.

- 입력으로 주어지는 table이 이미 visit_date가 오름차순인 상태로 주어지므로 따로 오름차순을 구현하지는 않았습니다.