LeetCode - 601. Human Traffic of Stadium (Oracle)

Hard - CTE, ROW_NUMBER

RunTime : 486 ms

문제

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.

The result format is in the following example.

풀이

• CTE를 통해 연속된 id를 그룹화한다.
  id - ROW_NUMBER() : 연속된 id를 같은 그룹으로 구성

• 당연히 이때, WHERE 조건을 통해 people >= 100을 고려함

• 이후 메인 쿼리에서 같은 그룹 번호는 받은 id들에 대해
  COUNT()가 3개 이상인 경우만 확인

WITH C1 AS (
    SELECT  id,
            visit_date,
            people,
            id - ROW_NUMBER() OVER (ORDER BY id) AS grp
    FROM    Stadium 
    WHERE   people >= 100
)
SELECT  id,
        TO_CHAR(visit_date, 'YYYY-MM-DD') visit_date,
        people
FROM    C1
WHERE   grp IN (
    SELECT  grp 
    FROM    C1
    GROUP BY grp
    HAVING COUNT(*) >= 3
)
ORDER BY visit_date;