Formatted question description: https://leetcode.ca/all/601.html

601. Human Traffic of Stadium

Level

Hard

Description

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.

Solution

Suppose three consecutive rows in table are s1, s2 and s3 respectively. Each such row has people at least 100. Select all the fields of s1, where s1 can be at any position among s1, s2 and s3.

# Write your MySQL query statement below
select distinct s1.* from stadium s1, stadium s2, stadium s3
    where ((s1.id = s2.id - 1 and s2.id = s3.id - 1) or
           (s1.id = s2.id + 1 and s1.id = s3.id - 1) or
           (s1.id = s3.id + 1 and s2.id = s3.id - 1))
        and (s1.people >= 100 and s2.people >= 100 and s3.people >= 100)
    order by s1.id;

All Problems

All Solutions