Leetcode: Human Traffic of Stadium(SQL)

This question is asked at Amazon, Adobe, and Uber. 

We have a table called stadium with 3 columns: id, visit date, and people. No 2 rows have the same visit date, and as the id increases, the dates increased as well. 

We want to write a SQL query to display the records with 3 or more rows with consecutive ids and the number of people is greater than or equal to 100 for each. We want to return the result table ordered by visit_date in ascending order. So at least 100 people are visiting with 3 or more consecutive ids. 

This could be solved using a join or we can look at the next row looking at the current row. 

We select from the stadium 3 times, then join on Stadium s2 and then again on s3.

We are trying to find something with the next row of the previous row. This can be done using a self-join.

Our join condition is finding consecutive rows. 

Here's the solution: 

# Write your MySQL query statement below

SELECT DISTINCT s1.*

FROM Stadium s1 JOIN Stadium s2 JOIN Stadium s3

ON(s1.id = s2.id - 1 AND s1.id = s3.id - 2) OR

(s1.id = s2.id + 1 AND s1.id = s3.id - 1) OR

(s1.id = s2.id + 1 AND s1.id = s3.id + 2)

WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100

ORDER BY visit_date

So it joins 3 SQL tables to look for indexes, look for indices of consecutive, checks the condition then order by the visiting date. And that's pretty much it.