# 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.

### 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
)

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)

Rating: 1 out of 5.

Posted in SQL