hard_601.sql

/*
## hard: 601. Human Traffic of Stadium

### TASK 

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: # Write your MySQL query statement below
SELECT
    DISTINCT s1.id,
    s1.visit_date,
    s1.people
FROM 
    stadium AS s1,
    stadium AS s2,
    stadium AS s3
WHERE 
    s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100 AND
    ((s1.id + 1 = s2.id AND s2.id + 1 = s3.id) OR
     (s1.id - 1 = s2.id AND s2.id + 2 = s3.id) OR
     (s1.id - 2 = s2.id AND s2.id + 1 = s3.id))
ORDER BY 
    s1.id
;