601. Human Traffic of Stadium / LeetCode, SQL, MS SQL

문제

source: LeetCode

Write an SQL query 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.

 

 

 

 

제출답안(MySQL)

WITH std AS (
    SELECT 
        id, visit_date, people, id - ROW_NUMBER() OVER(ORDER BY id) AS row_num
    FROM stadium
    WHERE people >= 100
),

std_cons AS (
    SELECT row_num
    FROM std
    GROUP BY row_num
    HAVING COUNT(id) >= 3
)

SELECT id, visit_date, people
FROM std
WHERE row_num IN (SELECT row_num FROM std_cons)

 

 

 

 

문제 바로가기(MySQL)