601. Human Traffic of Stadium | Hard 🔥

문제

 

Write a solution to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each. Return the result table ordered by visit_date in ascending order.

테이블 정보

 

예시

 

'id'가 연속적인 세 개 이상의 행을 찾되, 각 행의 'people'값이 100 이상인 경우에 해당하는 레코드를 출력하는 문제이다.

 

💡연속적이라는 단서를 보면 self-join을 통해 접근해 보자!

 

 

Solution (1)

 

✔️ Stadium 테이블을 셀프 조인하여 세 개의 연속된 id 확인

SELECT *
FROM Stadium s1
    JOIN Stadium s2 ON s1.id + 1 = s2.id 
    JOIN Stadium s3 ON s2.id + 1 = s3.id

조인 결과

 

✔️ 각 행의 'people' 값이 100 이상인 경우만 필터링

SELECT s1.*
FROM Stadium s1
    JOIN Stadium s2 ON s1.id + 1 = s2.id 
    JOIN Stadium s3 ON s2.id + 1 = s3.id 
WHERE s1.people >= 100
  AND s2.people >= 100
  AND s3.people >= 100

UNION

SELECT s2.*
FROM Stadium s1
    JOIN Stadium s2 ON s1.id + 1 = s2.id 
    JOIN Stadium s3 ON s2.id + 1 = s3.id 
WHERE s1.people >= 100
  AND s2.people >= 100
  AND s3.people >= 100

UNION 

SELECT s3.*
FROM Stadium s1
    JOIN Stadium s2 ON s1.id + 1 = s2.id 
    JOIN Stadium s3 ON s2.id + 1 = s3.id 
WHERE s1.people >= 100
  AND s2.people >= 100
  AND s3.people >= 100
ORDER BY visit_date

 

 

Solution (2)

 

✔️ LEAD(), LAG() 함수를 사용하여 id 값의 연속성을 확인

SELECT *
     , LEAD(id, 1) OVER (ORDER BY id) AS next_id
     , LEAD(id, 2) OVER (ORDER BY id) AS second_next_id
     , LAG(id, 1) OVER (ORDER BY id) AS last_id
     , LAG(id, 2) OVER (ORDER BY id) AS second_last_id
FROM Stadium
WHERE people >= 100

CTE 결과

 

✔️ id 값이 연속적인 세 개 id 값들 중에서 가장 작은 경우, 중간인 경우, 가장 큰 경우 각각을 고려

-- ❗처음에 LEAD/LAG 접근할 때 people 값을 이용했더니 길이 안보였다 ... 

-- ✅ id 기준으로 세 가지 경우의 수를 고려한다. id가 최소/중간/최대 각각 
WITH preprocessed AS (
SELECT *
     , LEAD(id, 1) OVER (ORDER BY id) AS next_id
     , LEAD(id, 2) OVER (ORDER BY id) AS second_next_id
     , LAG(id, 1) OVER (ORDER BY id) AS last_id
     , LAG(id, 2) OVER (ORDER BY id) AS second_last_id
FROM Stadium
WHERE people >= 100

)
SELECT DISTINCT id
     , visit_date
     , people
FROM preprocessed
WHERE (next_id - id = 1 AND second_next_id - next_id = 1) -- id < next < second_next
   OR (id - last_id = 1 AND next_id - id = 1) -- last < id < next
   OR (last_id - second_last_id = 1 AND id - last_id = 1) -- second_last < last < id

 

 

 

💡비슷한 아이디어를 얻을 수 있는 문제

https://hwangbyeongho.tistory.com/68

180. Consecutive Numbers | Medium