Date: February 16, 2026
Category: SQL
Time Taken: 15 minutes
Difficulty: Hard

---

Problem Statement

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.

Link: Human Traffic of Stadium

---

Approach 1 (LAG/LEAD):

• The first solution that came to mind was to use the LAG/LEAD functions. These allow us to check the next and the one after that, as well as the previous and the one before that.

• However, this approach becomes repetitive and doesn't scale well if we need to check consecutive rows for 10 instead of just 3.

Solution Code:

WITH HighTraffic AS (
    SELECT * FROM Stadium WHERE people >= 100
),
CheckedRows AS (
    SELECT *,
           LAG(id, 1) OVER(ORDER BY id) as p1,
           LAG(id, 2) OVER(ORDER BY id) as p2,
           LEAD(id, 1) OVER(ORDER BY id) as n1,
           LEAD(id, 2) OVER(ORDER BY id) as n2
    FROM HighTraffic
)
SELECT id, visit_date, people
FROM CheckedRows
WHERE (id = p1 + 1 AND id = p2 + 2)
   OR (id = p1 + 1 AND id = n1 - 1)
   OR (id = n1 - 1 AND id = n2 - 2)
ORDER BY id;

---

Approach 2 (ROW_NUMBER):

• The best approach for this problem is to use ROW_NUMBER() instead of LAG/LEAD to calculate group IDs for each ID.

• The IDs will always increase in ascending order, but we will filter out any where people < 100.

• After filtering, we use the difference between the ID and ROW_NUMBER() to determine the group number they belong to.

• This solution is the most scalable.

Solution Code:

WITH consecutive_groups AS (
    SELECT 
        id, 
        visit_date, 
        people,
        id - ROW_NUMBER() OVER(ORDER BY id ASC) AS grouped_id
    FROM Stadium
    WHERE people >= 100
)

SELECT id, visit_date, people
FROM consecutive_groups
WHERE grouped_id IN (
    SELECT grouped_id
    FROM consecutive_groups
    GROUP BY grouped_id
    HAVING COUNT(*) >= 3
)
ORDER BY visit_date ASC

Pattern: Window Functions | Islands and Gaps

Mistakes I Made: None, Easy problem.

Series: 90 Days of Data Engineering Progress: 44/90 problems completed

Tags: #DEQuest #LeetCode #SQL #DataEngineering #BuildInPublic