Human Traffic of Stadium - Practice Coding Problems

Human Traffic of Stadium - Problem

Database Hard

🏟️ Stadium Traffic Analysis

You're managing a stadium's visitor analytics system. Given a database table containing daily visitor records, you need to identify periods of sustained high traffic - specifically, any sequence of three or more consecutive days where each day had at least 100 visitors.

The Stadium table contains:

id - Unique identifier (auto-incrementing with date)
visit_date - Date of visit
people - Number of visitors that day

Goal: Find all records that are part of a streak of 3+ consecutive days with ≥100 visitors each day.

Output: Return matching records ordered by visit_date in ascending order.

Input & Output

example_1.sql — Basic Consecutive Sequence

$ Input: Stadium: | id | visit_date | people | |----|------------|--------| | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 |

› Output: | id | visit_date | people | |----|------------|--------| | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 |

💡 Note: Records with IDs 5,6,7,8 form a consecutive sequence of 4 days, each with ≥100 people. Records 2,3 only form a sequence of 2 days (not enough), and other records don't meet the people threshold.

example_2.sql — Multiple Valid Sequences

$ Input: Stadium: | id | visit_date | people | |----|------------|--------| | 1 | 2017-01-01 | 150 | | 2 | 2017-01-02 | 200 | | 3 | 2017-01-03 | 180 | | 4 | 2017-01-04 | 50 | | 5 | 2017-01-05 | 120 | | 6 | 2017-01-06 | 130 | | 7 | 2017-01-07 | 140 |

› Output: | id | visit_date | people | |----|------------|--------| | 1 | 2017-01-01 | 150 | | 2 | 2017-01-02 | 200 | | 3 | 2017-01-03 | 180 | | 5 | 2017-01-05 | 120 | | 6 | 2017-01-06 | 130 | | 7 | 2017-01-07 | 140 |

💡 Note: Two separate consecutive sequences: IDs 1,2,3 (3 days) and IDs 5,6,7 (3 days). ID 4 breaks the sequence with only 50 people.

example_3.sql — No Valid Sequences

$ Input: Stadium: | id | visit_date | people | |----|------------|--------| | 1 | 2017-01-01 | 150 | | 2 | 2017-01-02 | 200 | | 3 | 2017-01-03 | 50 | | 4 | 2017-01-04 | 180 | | 5 | 2017-01-05 | 120 |

› Output: | id | visit_date | people | |----|------------|--------| (empty result)

💡 Note: No consecutive sequence of 3+ days exists where all days have ≥100 people. The longest valid sequences are only 2 days (IDs 1,2 and IDs 4,5).

Visualization

Tap to expand

Understanding the Visualization

Filter High Traffic

First, we filter out days with low attendance (< 100 people)

Detect Patterns

Use the mathematical property: consecutive IDs have constant (ID - ROW_NUMBER) when sorted

Group & Validate

Group records with same (ID - ROW_NUMBER) value and keep groups with 3+ records

Return Results

Output all records from valid groups, sorted by date

Key Takeaway

🎯 Key Insight: The difference between ID and ROW_NUMBER remains constant for consecutive records, allowing us to group them efficiently without complex joins!

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting for window function, much more efficient than joins

⚡ Linearithmic

Space Complexity

O(n)

Linear space for storing intermediate grouping results

⚡ Linearithmic Space

Constraints

1 ≤ Stadium table rows ≤ 10⁴
1 ≤ id ≤ 10⁴
people ≥ 0
visit_date is a valid date
id increases with visit_date (chronological order)
All visit_dates are unique

Asked in

f Facebook 35 G Google 28 a Amazon 22 ⊞ Microsoft 18

The optimal solution uses a window function approach with the key insight that consecutive records have a constant difference between their ID and row number. This allows us to group consecutive sequences efficiently in O(n log n) time, much faster than the brute-force self-join approach.

Common Approaches

Approach	Time	Space	Notes
✓ Self-Join Approach (Brute Force)	O(n³)	O(n²)	Join the table with itself multiple times to find consecutive records
Window Function Approach (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to detect consecutive sequences in one pass

Self-Join Approach (Brute Force) — Algorithm Steps

Filter records with people >= 100
Self-join the table to find records with consecutive IDs
Check for sequences of 3 or more consecutive days
Remove duplicates and order by visit_date

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter High Traffic Days

First, filter all records where people >= 100

Triple Self-Join

Join table with itself to find consecutive triplets

Union Results

Combine all records that are part of valid sequences

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n³)

Multiple self-joins with nested comparisons result in cubic complexity

⚠ Quadratic Growth

Space Complexity

O(n²)

Intermediate join results can be quadratic in size

⚠ Quadratic Space

Constraints

1 ≤ Stadium table rows ≤ 10⁴
1 ≤ id ≤ 10⁴
people ≥ 0
visit_date is a valid date
id increases with visit_date (chronological order)
All visit_dates are unique

21.0K Views

High Frequency

~25 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🏟️ Stadium Traffic Analysis

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Self-Join Approach (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler