Amazon SQL interview questions – Leetcode 601 – Human Traffic of Stadium

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)