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.

Logic:

Here in the problem, the “people” more than 100 meant the records with people more than 100, which is the single record, not the sum of the records.\

The use the three possible combination of id’s for the ids in the outcome table

Solution:

select id, to_char(visit_date,'YYYY-MM-DD') as visit_date, people 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) and
id-1 in (select id from stadium where people >= 100)) or
(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 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