Stadium High-Attendance Sequences

HARD30 pts

You are given a Stadium table with the following columns:

id: unique visit identifier
visit_date: date of the visit (unique)
people: attendance count

Business rule:

A row is high attendance if people >= 100.
You must return rows that belong to any streak of at least 3 consecutive ids where every row in the streak is high attendance.

Output requirements:

Return exactly: id, visit_date, people
Order by visit_date ascending

Important:

Consecutive means by id, not by date gaps.

Supported submissions:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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-09 | 188    |

Output

[
  {"id":5,"visit_date":"2017-01-05","people":145},
  {"id":6,"visit_date":"2017-01-06","people":1455},
  {"id":7,"visit_date":"2017-01-07","people":199},
  {"id":8,"visit_date":"2017-01-09","people":188}
]

Explanation

Ids 5-8 form a consecutive-id high-attendance run of length 4. Ids 2-3 are high attendance but length 2 only, so they are excluded.

Example

Input

Stadium:
| id | visit_date | people |
|----|------------|--------|
| 10 | 2024-06-01 | 100    |
| 11 | 2024-06-02 | 101    |
| 12 | 2024-06-03 | 102    |
| 13 | 2024-06-04 | 80     |

Output

[
  {"id":10,"visit_date":"2024-06-01","people":100},
  {"id":11,"visit_date":"2024-06-02","people":101},
  {"id":12,"visit_date":"2024-06-03","people":102}
]

Explanation

Ids 10-12 are exactly one valid streak of length 3.

Example

Input

Stadium:
| id | visit_date | people |
|----|------------|--------|
| 20 | 2024-08-01 | 130    |
| 21 | 2024-08-02 | 70     |
| 22 | 2024-08-03 | 140    |
| 23 | 2024-08-04 | 150    |

Output

[]

Explanation

No high-attendance consecutive-id streak reaches length 3.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Stadium <= 50,000
1 <= id <= 1,000,000,000
0 <= people <= 1,000,000,000
visit_date is unique
As id increases, visit_date increases
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return columns: id, visit_date, people

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

stadium =

[{"id":1,"visit_date":"2017-01-01","people":10},{"id":2,"visit_date":"2017-01-02","people":109},{"id":3,"visit_date":"2017-01-03","people":150},{"id":4,"visit_date":"2017-01-04","people":99},{"id":5,"visit_date":"2017-01-05","people":145},{"id":6,"visit_date":"2017-01-06","people":1455},{"id":7,"visit_date":"2017-01-07","people":199},{"id":8,"visit_date":"2017-01-09","people":188}]

Stadium High-Attendance Sequences

Hints

Stadium High-Attendance Sequences

Hints