Skip to main content
Talentd

Human Traffic of Stadium - Solution & Explanation

HardDatabase7 min readAsked at: Amazon, Meta, Uber +3
Practice this problem

Problem Statement

Table: Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date is the column with unique values for this table.
Each row of this table contains the visit date and visit id to the stadium with the number of people during the visit.
As the id increases, the date increases as well.

 

Write a solution to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each.

Return the result table ordered by visit_date in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Stadium 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-09 | 188       |
+------+------------+-----------+
Output: 
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
Explanation: 
The four rows with ids 5, 6, 7, and 8 have consecutive ids and each of them has >= 100 people attended. Note that row 8 was included even though the visit_date was not the next day after row 7.
The rows with ids 2 and 3 are not included because we need at least three consecutive ids.

Approach Overview

Problem Overview: The Human Traffic of Stadium problem asks you to return stadium records that belong to a streak of at least three consecutive days where the number of visitors is 100 or more. The result should include every row that participates in such a consecutive sequence.

Approach 1: Iterative Comparison (O(n) time, O(1) space)

Start by filtering records where people >= 100. Sort by id or visit_date to maintain chronological order. Iterate through the filtered list and check if three adjacent rows have consecutive IDs (e.g., id, id+1, id+2). If the condition holds, include those rows in the result set. This approach works well in scripting languages like Python where you can scan arrays directly. The key insight is that consecutive traffic days form contiguous segments after filtering.

Approach 2: Sliding Window Method (O(n) time, O(1) space)

Treat the filtered dataset as a sequence and maintain a window of three records. Move the window one step at a time and check whether the IDs are consecutive and each row has people >= 100. When the window satisfies the constraint, mark all elements inside the window as valid. This technique avoids repeated comparisons and is a common pattern in problems involving contiguous segments, similar to techniques discussed in sliding window problems.

Approach 3: SQL Window Functions (O(n) time, O(n) space)

SQL provides a clean solution using LAG() and LEAD() window functions. After filtering rows where people >= 100, compare each row with its neighbors to check if three consecutive IDs exist. Another common pattern groups rows using id - ROW_NUMBER() to identify consecutive sequences. Once groups are formed, keep only groups with size ≥ 3. Window functions make this approach concise and efficient when working with relational datasets, which is why it frequently appears in SQL and database interview questions.

Approach 4: SQL-like Filter and Sort Strategy (O(n log n) time, O(n) space)

In JavaScript or other general-purpose languages, you can mimic SQL behavior by filtering rows with people >= 100, sorting them by ID, and grouping consecutive IDs into segments. Track segment boundaries by checking whether current.id == previous.id + 1. Once a segment length reaches three or more, add all rows from that segment to the result. This approach mirrors how relational engines process ordered datasets.

Recommended for interviews: The SQL window function solution is typically expected in database-focused interviews because it demonstrates familiarity with LAG, LEAD, and sequence grouping techniques. The sliding window or iterative scan shows the same logic in algorithmic form and proves you understand how to detect consecutive segments efficiently. Demonstrating both perspectives signals strong problem-solving ability across SQL and general programming environments.

Approach 1: Using SQL Window Functions

This approach utilizes SQL window functions to help identify consecutive rows with the required conditions. By computing a lag on the 'people' field, we can identify groups of qualifying rows by checking the conditions on currently selected and previous rows.

This SQL solution makes use of the LAG function to look into previous rows' people counts. The three consecutive rows meeting the criteria are filtered using WHERE conditions.

Code

SQL

SQL

Complexity

The time complexity of this query is O(n), where n is the number of rows because each row is processed once. The space complexity is also O(n) due to the use of window functions.

Try this approach in the editor →

Approach 2: Iterative Comparison

In this approach, we manually iterate over the records, maintaining a sliding window of three elements to check the condition on people counts. This involves checking the count of the previous two IDs.

This Python solution iterates over the input data with an index starting from the third row and checks if the current and the previous two rows have a people count of at least 100. If valid, the rows are then added to the result, ensuring no duplicates using dictionary keys to preserve order.

Code

Python

Python

Complexity

The time complexity of this approach is O(n), where n is the length of the dataset, as each row is visited once. The space complexity is O(1) for storing temporary variables outside of external list storage.

Try this approach in the editor →

Approach 3: Approach 1: Sliding Window Method

This approach utilizes a sliding window or similar mechanism to iterate over the rows and assess whether consecutive rows satisfy the conditions.

The solution traverses the list of visits and uses a sliding window approach to check for three or more consecutive days where the number of people in attendance is greater than or equal to 100. If the condition is met, it appends those days to the results list. Finally, the results are deduplicated and sorted by visit date.

Code

Python

Python

Complexity

Time Complexity: O(n), where n is the number of records in the stadium table.
Space Complexity: O(n) due to the result storage.

Try this approach in the editor →

Approach 4: Approach 2: SQL-like Filter and Sort Strategy

This approach employs a segmenting strategy, similar to using SQL WHERE clauses, to filter and then sort the data based on conditions using language-specific features.

The JS solution iterates over the stadium data and counts consecutive entries with more than or equal to 100 people. Once the count reaches three, it gathers these entries into a result list. Finally, the results are sorted based on date before returning.

Code

JavaScript

JavaScript

Complexity

Time Complexity: O(n log n), primarily due to the sorting operation.
Space Complexity: O(n) as it stores the results list.

Try this approach in the editor →

Approach 5: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Using SQL Window Functions

The time complexity of this query is O(n), where n is the number of rows because each row is processed once. The space complexity is also O(n) due to the use of window functions.

Iterative Comparison

The time complexity of this approach is O(n), where n is the length of the dataset, as each row is visited once. The space complexity is O(1) for storing temporary variables outside of external list storage.

Approach 1: Sliding Window Method

Time Complexity: O(n), where n is the number of records in the stadium table.
Space Complexity: O(n) due to the result storage.

Approach 2: SQL-like Filter and Sort Strategy

Time Complexity: O(n log n), primarily due to the sorting operation.
Space Complexity: O(n) as it stores the results list.

Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Iterative ComparisonO(n)O(1)Simple array-based processing in Python or similar languages
Sliding Window MethodO(n)O(1)When scanning ordered records for fixed-size consecutive segments
SQL Window FunctionsO(n)O(n)Best choice for SQL interviews and relational database queries
SQL-like Filter and Sort StrategyO(n log n)O(n)Useful in JavaScript or environments without SQL window functions

Video Solution

Leetcode Hard SQL Problem | Human Traffic of Stadium • Ankit Bansal • 44,647 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Human Traffic of Stadium easy or hard?
Human Traffic of Stadium is labeled Hard because it requires recognizing patterns in consecutive records and implementing them with SQL window functions or grouping logic. The algorithm itself is linear, but the challenge lies in expressing the sequence detection correctly in SQL.
How to solve Human Traffic of Stadium in O(n)?
Filter rows where people >= 100, then scan the sorted records to detect consecutive IDs. Using a sliding window of size three or grouping rows by id minus row_number allows you to identify sequences efficiently. Once a sequence length reaches three, return all rows belonging to that group.
Human Traffic of Stadium Python or Java solution
Python solutions typically filter records with people >= 100 and iterate through the sorted list to check consecutive IDs using a sliding window or pointer scan. JavaScript and Java implementations follow the same logic: filter, sort, group consecutive IDs, and return segments with length at least three.
What is the best approach for Human Traffic of Stadium?
The most efficient solution uses SQL window functions such as LAG(), LEAD(), or row-number grouping to detect consecutive rows where people >= 100. This runs in O(n) time over the dataset and clearly identifies sequences of at least three consecutive records. It is the preferred approach in database interviews.
Is Human Traffic of Stadium asked at Google/Amazon/Meta?
This style of SQL problem appears frequently in data engineering and analytics interviews at companies like Amazon, Google, and Meta. It tests understanding of window functions, sequence grouping, and filtering conditions in relational datasets.
What data structure is used in Human Traffic of Stadium?
In SQL solutions, the main tools are window functions and ordered result sets rather than traditional data structures. In algorithmic implementations, arrays or lists store the filtered records, and consecutive comparisons detect valid segments.
What is the time complexity of Human Traffic of Stadium?
Most optimal solutions run in O(n) time because each row is processed once. SQL window function solutions and sliding window scans both achieve linear complexity. Space complexity ranges from O(1) in iterative scans to O(n) when intermediate groups or window results are stored.

Ready to solve this problem?

Practice Human Traffic of Stadium with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary