Problem Description –
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.
The query result format is in the following example.

Difficulty Level – Hard
Solution –
WITH cte AS(
SELECT
id ,
visit_date,
people,
LEAD(people, 1) OVER(ORDER BY id) as next,
LEAD(people, 2) OVER(ORDER By id) as next2,
LAG(people, 1) OVER(ORDER BY id ) as prev,
LAG(people, 2) OVER(ORDER BY id) as prev2
FROM Stadium
)
SELECT
id,
visit_date,
people
FROM cte
WHERE (cte.people >= 100 AND cte.next >= 100 AND cte.next2>=100)
OR (cte.people >= 100 AND cte.next >= 100 AND cte.prev >= 100)
OR (cte.people >= 100 AND cte.prev >=100 AND cte.prev2 >=100)
ORDER BY visit_date
The query in the common table expression gives us the following output –

We are leading the people column once by one and the second time by 2, we are also lagging the people column once by 1 and the second time by 2.
Then in the final query, we are using three conditions to get the desired result –
WHERE (cte.people >= 100 AND cte.next >= 100 AND cte.next2>=100)
OR (cte.people >= 100 AND cte.next >= 100 AND cte.prev >= 100)
OR (cte.people >= 100 AND cte.prev >=100 AND cte.prev2 >=100)