Leetcode SQL

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 >= 100and (id+1 in (select id from stadium where people >= 100) andid in (select id from stadium where people >= 100) andid-1 in (select id from stadium where people >= 100)) or(id+2 in (select id from stadium where people >= 100) andid+1 in (select id from stadium where people >= 100) andid in (select id from stadium where people >= 100)) or(id in (select id from stadium where people >= 100) andid-1 in (select id from stadium where people >= 100) andid-2 in (select id from stadium where people >= 100))order by id, visit_date;`

--

--

--

More from JEN-LI CHEN IN DATA SCIENCE

My homepage to record my thought processes for solving SQL and Algorithm questions

Jen-Li Chen

In love with telling stories with data