601_Human Traffic of Stadium

[hard]

X city built a new stadium, each day many people visit it and the stats are saved as these columns:id,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   | 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   | 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 1: using self join 3 times

385 ms, 82%

SELECT DISTINCT s1.*
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 - s2.id = 1 AND s1.id - s3.id = 2 )  -- for rows in first place
    OR
    (s2.id - s1.id = 1 AND s2.id - s3.id = 2 )  -- for rows in second place
    OR
    (s3.id - s2.id = 1 AND s2.id - s1.id =1 )   -- for rows in third place
)
ORDER BY s1.id
;

Solution 2: using self join 5 times

SELECT t.* 
FROM stadium t
    LEFT JOIN stadium p1 ON t.id - 1 = p1.id
    LEFT JOIN stadium p2 ON t.id - 2 = p2.id
    LEFT JOIN stadium n1 ON t.id + 1 = n1.id
    LEFT JOIN stadium n2 ON t.id + 2 = n2.id
WHERE (t.people >= 100 AND p1.people >= 100 AND p2.people >= 100)
     OR (t.people >= 100 AND n1.people >= 100 AND n2.people >= 100)
     OR (t.people >= 100 AND n1.people >= 100 AND p1.people >= 100)
ORDER BY id;

Solution 3: using UNION

386 ms, 82%

SELECT id, date, people
FROM (
    SELECT s1.id AS id, s1.date AS date, s1.people AS people
    FROM stadium AS s1
        JOIN stadium AS s2 ON s2.id - s1.id = 1
        JOIN stadium AS s3 ON s3.id - s2.id = 1
    WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
    ) AS New
UNION
SELECT id, date, people
FROM (
    SELECT s2.id AS id, s2.date AS date, s2.people AS people
    FROM stadium AS s1
        JOIN stadium AS s2 ON s2.id - s1.id = 1
        JOIN stadium AS s3 ON s3.id - s2.id = 1
    WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
    ) AS New
UNION
SELECT id, date, people
FROM (
    SELECT s3.id AS id, s3.date AS date, s3.people AS people
    FROM stadium AS s1
        JOIN stadium AS s2 ON s2.id - s1.id = 1
        JOIN stadium AS s3 ON s3.id - s2.id = 1
    WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
    ) AS New
ORDER BY id
;