Leetcode 601. 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, visit_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).

 

Create table stadium (id int, visit_date DATE NULL, people int)
Truncate table stadium
insert into stadium (id, visit_date, people) values ('1', '2017-01-01', '10')
insert into stadium (id, visit_date, people) values ('2', '2017-01-02', '109')
insert into stadium (id, visit_date, people) values ('3', '2017-01-03', '150')
insert into stadium (id, visit_date, people) values ('4', '2017-01-04', '99')
insert into stadium (id, visit_date, people) values ('5', '2017-01-05', '145')
insert into stadium (id, visit_date, people) values ('6', '2017-01-06', '1455')
insert into stadium (id, visit_date, people) values ('7', '2017-01-07', '199')
insert into stadium (id, visit_date, people) values ('8', '2017-01-08', '188')

select * from stadium

Solution:

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 - s2.id = 1 and s1.id - s3.id = 2 and s2.id - s3.id =1)
or
(s2.id - s1.id = 1 and s2.id - s3.id = 2 and s1.id - s3.id =1)
or
(s3.id - s2.id = 1 and s2.id - s1.id = 1 and s3.id - s1.id =2)
)