Skip to content

601. Human Traffic of Stadium

Approach 1: Window function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH
  StadiumNeighbors AS (
    SELECT
      id,
      visit_date,
      people,
      LAG(people, 1) OVER(ORDER BY id) AS prev_people_1,
      LAG(people, 2) OVER(ORDER BY id) AS prev_people_2,
      LEAD(people, 1) OVER(ORDER BY id) AS next_people_1,
      LEAD(people, 2) OVER(ORDER BY id) AS next_people_2
    FROM Stadium
  )
SELECT
  id,
  visit_date,
  people
FROM StadiumNeighbors
WHERE
  people >= 100 AND (
    prev_people_1 >= 100 AND prev_people_2 >= 100
    OR prev_people_1 >= 100 AND next_people_1 >= 100
    OR next_people_1 >= 100 AND next_people_2 >= 100
  )
ORDER BY visit_date;

Approach 2: ROW_NUMBER()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
WITH
  StadiumWithGroupId AS (
    SELECT
      id,
      visit_date,
      people,
      id - ROW_NUMBER() OVER(ORDER BY id) AS group_id
    FROM Stadium
    WHERE people >= 100
  )
SELECT id, visit_date, people
FROM StadiumWithGroupId
WHERE group_id IN (
    SELECT group_id
    FROM StadiumWithGroupId
    GROUP BY group_id
    HAVING COUNT(*) >= 3
  )
ORDER BY visit_date;