SQL- Human Traffic of Stadium: general N consecutive days query

Human Traffic of Stadium

Hard

 

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:

 

Note:
Each day only have one row record, and the dates are increasing with id increasing.

 

ANALYSIS:

The question asks to print out the information when there were greater than 100 people (inclusive) visited the stadium.

 

This kind of question (find N consecutive) is shown in many SQL interview tests serval times. Hence, it is worth taking some time to really comprehend.

 

 SELECT 
    id, visit_date, people
 FROM
    (SELECT 
        id, visit_date, people,
        @pending := 
            CASE 
            # here 3 is flexible. That is, if a question asks for N days then set count >= N and @pending := N
            # another key point of this algo is to subtract 1 for any days that less than given condition, here 3.
                WHEN count >= 3  THEN @pending := 3
                ELSE @pending -1
            END as pending, 
        # declaring a new var called include to parse the only rows meet the condition: 3 consecutive days and >= 100 people
        IF(@pending > 0, 1, 0) as include
    FROM     
        (SELECT 
            id, visit_date, people,
            # set up a variable called count that tracks rows meet the condition >= 100
            # it is important to do @cnt + 1 instead of simply assigning 1
            @cnt := 
                CASE 
                    WHEN people >= 100 THEN @cnt + 1
                    ELSE 0 
                END as count 
        FROM 
            stadium, 
            (SELECT @cnt := 0 )f
        ) temp, 
        (SELECT @pending := 0) f2
    # last key point of this algo, is flip the order to subtract them properly. 
    ORDER BY 
        id DESC
    ) temp2
WHERE
    include = 1
ORDER BY 
    id