[LeetCode] 601. Human Traffic of Stadium (연속된 id 가져오기)

리트코드에 무료로 나와있는 MEDIUM 레벨의 문제를 모두 해결해서

오늘부터는 HARD 레벨의 문제를 풀며 고급 SQL 문제를 복기 겸 연습해보겠습니다!

 

🔎 문제

 

📍 문제 링크: https://leetcode.com/problems/human-traffic-of-stadium/description/

 

연속된 ID를 가진 3개 이상의 행으로 레코드를 표시하는 솔루션을 작성하고, 각 행에 대해 100명 이상의 인원이 필요합니다. 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       |
+------+------------+-----------+

ID가 5, 6, 7, 8인 4행은 연속 ID를 가지며 각각 >= 100명이 참석했습니다. 

visit_date 7행의 다음 날이 아님에도 불구하고 8행이 포함되었습니다.
ID가 2와 3인 행은 최소 3개의 연속 ID가 필요하기 때문에 포함되지 않습니다.

 

 

💡 문제 풀이

 

WITH cte_1 AS(
    SELECT *
        , LAG(id, 2) OVER(ORDER BY id) as lag2_id
        , LAG(id, 1) OVER(ORDER BY id) as lag_id
        , LEAD(id, 1) OVER(ORDER BY id) as lead_id
        , LEAD(id, 2) OVER(ORDER BY id) as lead2_id
    FROM stadium
    WHERE people >= 100
)
SELECT id, visit_date, people
FROM cte_1
WHERE (id+1=lead_id AND lead_id+1=lead2_id)
    OR (id-1=lag_id AND id+1=lead_id)
    OR (id-1=lag_id AND id-2=lag2_id)

 

1️⃣ 앞, 뒤로 2개의 id를 가져온다.

      where절을 통해 people이 100 이상인 행만 가져온다.

 

 

2️⃣ id가 연속 3개인 행을 불러오기 위해서는 세 가지의 관계를 봐야 한다.

• id 뒤로 연속 두 개의 숫자가 있는 경우 (ex. 5,6,7)
• id가 중간인 상태로 연속한 id가 이어지는 경우 (ex. 6,7,8)
• id 앞으로 연속 두개의 숫자가 있는 경우 (ex. 6,7,8)

    세 가지 중 하나라도 만족하면 결과값으로 출력되도록 한다.

 

 

🔎 Solutions

 

이건 soultions에 올라와있는 풀이방법이다. 중간에 끊기는 숫자가 생길 때마다 id_diff의 값이 달라지는 원리를 사용했다. 나중에 유사한 문제가 나오면 사용해 봐야겠다.

 

with q1 
as (
    select *
        , id - row_number() over() as id_diff
    from stadium
    where people > 99
)
select id, visit_date, people
from q1
where id_diff in (select id_diff from q1 group by id_diff having count(*) > 2)
order by visit_date

 

1️⃣ row_number을 사용하여 각 행에 순번을 붙인다.

 

 

2️⃣ id에서 rou_number을 빼준 것을 id_diff로 이름 붙이고, id_diff를 기준으로 group by를 해주어 그 수가 2개 초과인 값을 불러온다.