Stadium Traffic Gaps
ASKED IN INTERVIEWClassic
Problem Statement
Identify sequences of 3 or more consecutive days where the stadium had 100 or more visitors.
Rules:
- Output columns: id, visit_date, people.
- Condition: people >= 100 for at least 3 days in a row.
- Sort by visit_date ascending.
Tests your understanding of
Window Functions, Gaps and Islands and Lead/Lag
Input Tables
stadium
| id(INTEGER) | visit_date(DATE) | people(INTEGER) |
|---|---|---|
| 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 |
| 8 | 2024-01-08 | 188 |
stadium_events
| event_id(INTEGER) | id(INTEGER) | event_name(VARCHAR) |
|---|---|---|
| 501 | 6 | Final Cup |
weather_logs
| id(INTEGER) | temp(INTEGER) | condition(VARCHAR) |
|---|---|---|
| 1 | 72 | Sunny |
Expected Output
| id(INTEGER) | visit_date(DATE) | people(INTEGER) |
|---|---|---|
| 5 | 2024-01-05 | 145 |
| 6 | 2024-01-06 | 1455 |
| 7 | 2024-01-07 | 199 |
| 8 | 2024-01-08 | 188 |
Tags
HardASKED IN INTERVIEWWindow FunctionsGaps and IslandsLead/Lag
30-40 min
44%