# solution using join

• ``````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;
``````

• @liupangzi Can you please explain the JOINS and why there is a specific combination in the WHERE clause? Thanks.

• @ravit.thapar-gmail.com

Let's start from a basic `LEFT JOIN`:

``````SELECT today.*, yesterday.* FROM stadium today
LEFT JOIN stadium yesterday ON today.id - 1 = yesterday.id;
``````

The statement `LEFT JOIN ... ON ... today.id - 1 = yesterday.id;` means we join field `people` of today and field `people` of yesterday into one row, the results are as followed:

id date people id date people
2 2017-01-02 109 1 2017-01-01 10
3 2017-01-03 150 2 2017-01-02 109
4 2017-01-04 99 3 2017-01-03 150
5 2017-01-05 145 4 2017-01-04 99
6 2017-01-06 1455 5 2017-01-05 145
7 2017-01-07 199 6 2017-01-06 1455
8 2017-01-08 188 7 2017-01-07 199
1 2017-01-01 10 (null) (null) (null)

We don't have any record of the day before `2017-01-01`, so the last row left with three `null`s. And this SQL helps us to find two consecutive days of which people more than 100(`JOIN` table yesterday and tomorrow):

``````SELECT today.*, yesterday.* FROM stadium today
LEFT JOIN stadium yesterday ON today.id - 1 = yesterday.id
LEFT JOIN stadium tomorrow ON today.id + 1 = tomorrow.id
WHERE (today.people >= 100 AND yesterday.people >= 100) OR (today.people >= 100 AND tomorrow.people >= 100);
``````

Similarly, this can be applied to 3 consecutive days by table `today` joining 4 tables: `yesterday`, `tomorrow`, `the_day_before_yesterday`, `the_day_after_tomorrow`:

``````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
``````

Here `p1` / `p2` means previous N days for short, and `n1` / `n2` means next n days for short, `t` for today.

After we get record from SQL above, filter records(there are three ways of 3 consecutive days of which more than 100 people, as the table I list below) with an additional `ORDER BY` should meet the requirement.

p2 p1 t n1 n2
true true true - -
- true true true -
- - true true true

And that's what the `WHERE` statement does.

Hope it helps! :-P

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.