LeetCode 601 - Human Traffic of Stadium

https://hzhou.me/leetcode/leetcode-human-traffic-of-stadium

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:

1 2 3 4 5 6 7 8 9 10 11 12

+------+------------+-----------+ | id | date | people | +------+------------+-----------+ | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+

For the sample data above, the output is:

1 2 3 4 5 6 7 8

+------+------------+-----------+ | id | date | people | +------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+

Note:
Each day only have one row record, and the dates are increasing with id increasing.

select distinct s1.*
from stadium s1, stadium s2, stadium s3
where s1.people >= 100 and
s2.people >= 100 and
s3.people >= 100 and
(
    (s1.id + 1 = s2.id and s1.id + 2 = s3.id) or
    (s1.id - 1 = s2.id and s1.id + 1 = s3.id) or
    (s1.id - 2 = s2.id and s1.id - 1 = s3.id)
)
​
order by s1.id asc;

https://knarfeh.com/2014/03/11/leetcode/Database/leetcode-601-Human-Traffic-of-Stadium/

SELECT s1.id as id, s1.date as date, s1.people as people
from stadium as s1, stadium as s2, stadium as s3 WHERE
((s1.id = s2.id-1 AND s2.id = s3.id-1) OR
(s2.id = s1.id-1 AND s1.id = s3.id-1) OR
(s2.id = s3.id-1 AND s3.id = s1.id-1)) AND
s1.people>=100 AND s2.people>=100 AND s3.people>=100
GROUP BY s1.id;

http://www.yuanrengu.com/index.php/201707132.html

https://www.youtube.com/watch?v=Kiz9RPo3Fdc