[LeetCode] 601. Human Traffic of Stadium

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH three_rows AS (
    SELECT *
        , COUNT(*) OVER (ORDER BY visit_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cnt1
        , MIN(people) OVER (ORDER BY visit_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS min1
        , COUNT(*) OVER (ORDER BY visit_date ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cnt2
        , MIN(people) OVER (ORDER BY visit_date ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min2
        , COUNT(*) OVER (ORDER BY visit_date ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS cnt3
        , MIN(people) OVER (ORDER BY visit_date ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS min3
    FROM Stadium
)

SELECT id
     , visit_date
     , people
FROM three_rows
WHERE (cnt1 >= 3 AND min1 >= 100)
   OR (cnt2 >= 3 AND min2 >= 100)
   OR (cnt3 >= 3 AND min3 >= 100)