Leetcode SQL

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

For example, the table stadium:

+------+------------+-----------+
| id   | visit_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:

+------+------------+-----------+
| id   | visit_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.

Hint:

1. If not using to_char(visit_date, ‘YYYY-MM-DD’) the visit_date would have time. So be sure to use to_char(,’YYYY-MM-DD’)
2. order by id, visit_date. Testing showed without visit_date, it would still work. But to play safe, remember to order by id, visit_date.

Solution:

select id, to_char(visit_date,'YYYY-MM-DD') as visit_date, people from stadium
where people >= 100
and 
(id+2 in (select id from stadium where people >= 100) and
id+1 in (select id from stadium where people >= 100) and
id in (select id from stadium where people >= 100)) or
(id+1 in (select id from stadium where people >= 100) and
id in (select id from stadium where people >= 100) and
id-1 in (select id from stadium where people >= 100)) or
(id in (select id from stadium where people >= 100) and
id-1 in (select id from stadium where people >= 100) and
id-2 in (select id from stadium where people >= 100))
order by id, visit_date;

Link