solution using join


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

  • 0
    R

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


  • 4

    @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 nulls. 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


Log in to reply
 

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