Simple and extensible solution. No Join, No Order, No Groupby.


  • 0
    J

    An extensible solution without join. Can be easily extended to 4, 5, .... consecutive rows. Some of the steps may need some optimization, since it is not running fast.

    SET @N := 1; 
    SET @prevD := NULL;
    SELECT DISTINCT S1.id, S1.date, S1.people 
    FROM Stadium S1, (
        SELECT id, days
        FROM (
            SELECT id, date, people,
                CASE
                    WHEN id - @prevD = 1 THEN @N := @N + 1
                    ELSE @N := 1
                END AS days, @PrevD := id   # update @PrevD, after the calculation
            FROM Stadium
            WHERE people >= 100) S
        WHERE days >= 3) S2
    WHERE S1.id IN (S2.id, S2.id - 1, S2.id -2); 
    

Log in to reply
 

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