Human Traffic of Stadium

Human Traffic of Stadium - Problem

Database Hard

You have a Stadium table that records the daily visit information to a stadium. Each row contains the visit date, visit id, and number of people who visited on that date.

Write a SQL solution to display records where:

There are three or more consecutive rows with consecutive IDs
Each of these rows has people ≥ 100

Return the result table ordered by visit_date in ascending order.

Table Schema

Stadium

Column Name	Type	Description
`id` PK	int	Visit ID (increases with date)
`visit_date`	date	Date of the visit (unique)
`people`	int	Number of people who visited

Primary Key: id

Note: As the id increases, the date increases as well. Each visit_date is unique.

Input & Output

Example 1 — Mixed Busy and Quiet Days

Input Table:

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:

The stadium has records with IDs 5, 6, 7, 8 that are consecutive and each has at least 100 people (145, 1455, 199, 188). This forms a group of 4 consecutive busy days, which meets the requirement of 3 or more.

Example 2 — No Qualifying Sequences

Input Table:

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

Output:

id	visit_date	people

💡 Note:

Although some days have 100+ people, there's no sequence of 3 or more consecutive days where each day has at least 100 people. IDs 2,3 have 100+ but that's only 2 consecutive days.

Constraints

1 ≤ id ≤ 1000
visit_date is unique for each row
0 ≤ people ≤ 10000
As id increases, visit_date increases as well

Visualization

Tap to expand

Asked in

a Amazon 12 G Google 8 M Microsoft 6

Use window functions with ROW_NUMBER() to identify consecutive sequences. Filter records with people ≥ 100, then group consecutive IDs using the difference between ID and row number to find sequences of 3 or more.

Table Schema

Stadium

Column Name	Type	Description
`id` PK	int	Visit ID (increases with date)
`visit_date`	date	Date of the visit (unique)
`people`	int	Number of people who visited

Primary Key: id

Note: As the id increases, the date increases as well. Each visit_date is unique.

Common Approaches

✓ Window Function with ROW_NUMBER

⏱️ Time: O(n log n) Space: O(n)

Filter records with people >= 100, then use ROW_NUMBER() to create groups of consecutive IDs. Count each group to find sequences of 3 or more.

Window Function with ROW_NUMBER — Algorithm Steps

Step 1: Filter records where people >= 100
Step 2: Use ROW_NUMBER() to detect consecutive ID sequences
Step 3: Group by the difference (id - row_number) to identify consecutive runs
Step 4: Keep only groups with 3+ records

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter

Keep people >= 100

ROW_NUMBER

Assign sequence numbers

Group

id - ROW_NUMBER creates groups

Count

Keep groups with 3+ records

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for window function and final ORDER BY

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for window calculations

⚡ Linearithmic Space

28.0K Views

Medium 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with ROW_NUMBER — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler