# 601. 体育馆的人流量
# https://leetcode-cn.com/problems/human-traffic-of-stadium/

# SQL架构
Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int);
Truncate table Stadium;
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188');

# Write your MySQL query statement below
select
    distinct s1.id,
    s1.visit_date,
    s1.people
from
    Stadium s1,
    Stadium s2,
    Stadium 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
            and s2.id - s3.id = 1
        )
        or (
            s2.id - s1.id = 1
            and s2.id - s3.id = 2
            and s1.id - s3.id = 1
        )
        or (
            s3.id - s2.id = 1
            and s2.id - s1.id = 1
            and s3.id - s1.id = 2
        )
    )
order by
    s1.id;

# clean-up
drop table Stadium;