Human Traffic of Stadium

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.

stadium tracks daily stadium attendance with columns id, visit_date, and people. Write a query to display the records with three or more consecutive ids where attendance (people) was 100 or greater. Return the rows ordered by visit_date ascending.

Requirements

• Include every row that belongs to an eligible run of 3 or more rows.
• Sort output by visit_date ascending.

Constraints

• id is an auto-incrementing integer key.
• visit_date is unique and sorted in chronological order with id.
• people is a non-negative integer.

Examples

Input: stadium id,visit_date,people 1,2024-01-01,10 2,2024-01-02,109 3,2024-01-03,150 4,2024-01-04,99 5,2024-01-05,145 6,2024-01-06,1455 7,2024-01-07,199 ... Output: id | visit_date | people 5 | 2024-01-05 | 145 6 | 2024-01-06 | 1455 7 | 2024-01-07 | 199 8 | 2024-01-08 | 188 Why this passes: Ids 5, 6, 7 and 8 are four consecutive days with at least 100 people, so all four are returned; ids 2 and 3 only make a run of two and are dropped.

Input: stadium id | visit_date | people 1 | 2024-02-01 | 120 2 | 2024-02-02 | 130 3 | 2024-02-03 | 140 Output: id | visit_date | people 1 | 2024-02-01 | 120 2 | 2024-02-02 | 130 3 | 2024-02-03 | 140 Why this passes: All three consecutive rows meet the attendance threshold, forming a complete run of three.

Topics: lakebench, sql, gaps-and-islands, consecutive.

More SQL interview questions · All interview problems · Learn data engineering