Human Traffic of Stadium

Human Traffic of Stadium

What is this problem about?

The Human Traffic of Stadium coding problem is a database challenge. You are given a Stadium table with id, visit_date, and people. You need to find all rows where there are three or more consecutive rows with at least 100 people in each row. The output should be ordered by visit_date.

Why is this asked in interviews?

Uber and Meta use this "Hard" SQL problem to evaluate a candidate's ability to analyze sequential data in a relational database. It tests your proficiency with Window Functions or self-joins to identify groups of consecutive records. This is a common requirement in data analysis for identifying trends, streaks, or anomalous behaviors over time.

Algorithmic pattern used

There are two main ways to solve this using Database interview patterns:

1. Window Functions (LAG/LEAD): For each row, check if the previous two rows and the next two rows meet the criteria. If a row is part of any three-row sequence $\ge 100$, it is included.
2. Grouping by ID Difference:
   • Filter the table for rows where people >= 100.
   • Use ROW_NUMBER() to generate a sequence.
   • The difference id - row_number will be the same for any consecutive sequence of IDs.
   • Group by this difference and filter for groups with COUNT(*) >= 3.

Example explanation

Table Data:

id	people
1	50
2	150
3	200
4	120
5	80

1. IDs with $\ge 100$: [2, 3, 4].
2. Are they consecutive? Yes (ids 2, 3, 4).
3. Are there at least 3? Yes. Result: Rows 2, 3, and 4 are returned.

Common mistakes candidates make

• Ignoring the "Three or More" rule: Returning only the exact start or end of a sequence instead of all rows in the sequence.
• Self-Join Complexity: Writing extremely nested joins that are hard to read and inefficient.
• Off-by-one: Errors in the logic used to check the neighbors of a row.

Interview preparation tip

Master the ROW_NUMBER() trick for finding consecutive sequences. It’s a very common pattern: val - ROW_NUMBER() OVER(ORDER BY val) creates a constant "group ID" for any sequence of consecutive values.