A simple solution


  • 10
    K
    SELECT s1.* FROM stadium AS s1, stadium AS s2, stadium as s3
        WHERE 
        ((s1.id + 1 = s2.id
        AND s1.id + 2 = s3.id)
        OR 
        (s1.id - 1 = s2.id
        AND s1.id + 1 = s3.id)
        OR
        (s1.id - 2 = s2.id
        AND s1.id - 1 = s3.id)
        )
        AND s1.people>=100 
        AND s2.people>=100
        AND s3.people>=100
    
        GROUP BY s1.id
    

  • 0
    S

    neat! one question: why we need to "group by s1.id"? thank you:)


  • 1
    M

    @shaniavina I think it's because if you join table in this way, there're will be duplicate rows in the table.

    You can also do in this way,

    SELECT Distinct s1.id, s1.date, s1.people
    FROM stadium s1, stadium s2, stadium s3
    WHERE ((s1.id-1= s2.id and s1.id-2 = s3.id)
    Or (s1.id+1 = s2.id and s1.id + 2= s3.id)
    Or (s1.id-1 = s2.id and s1.id+1 = s3.id))
    and s1.people>=100 and s2.people>=100 and s3.people>=100
    ORDER BY s1.id;


  • 0
    K

    Exactly. :) .


  • 1
    C

    Can you please explain why it can also handle cases when n > 3?


  • 0
    K

    @cimyntt
    If there are n>3 days, say we have n = 4.

    Notice we have three OR conditions,
    either s1 being the first day (s1.id + 1 = s2.id AND s1.id + 2 = s3.id),
    second day (s1.id - 1 = s2.id AND s1.id + 1 = s3.id)
    or the third day (s1.id - 2 = s2.id AND s1.id - 1 = s3.id).
    So three consecutive days meet the requirements would show up.

    But this will produce duplicate results, for example in 4 consecutive days, there would be two days overlapping, which would pop out twice. In this way, the group by would eliminate the duplicate records.

    Hope this help you.


  • 0
    B

    What's the runtime on this? I used a similar logic but with six sub-queries and one main query and got near 400ms (top 25%).

    I'm preparing for an interview and trying to understand the relative differences in the efficiency of self-joins vs. sub-queries.

    SELECT id, date, people
    FROM stadium a
    WHERE people >= 100 
            AND (SELECT people FROM stadium b WHERE b.id = a.id + 1) >= 100 
            AND (SELECT people FROM stadium c WHERE c.id = a.id + 2) >= 100 
        OR
          people >= 100
            AND (SELECT people FROM stadium e WHERE e.id = a.id - 1) >= 100
            AND (SELECT people FROM stadium f WHERE f.id = a.id + 1) >= 100
        OR
          people >= 100
            AND (SELECT people FROM stadium g WHERE g.id = a.id - 1) >= 100
            AND (SELECT people FROM stadium h WHERE h.id = a.id - 2) >= 100
    

Log in to reply
 

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