Stadium High-Attendance Sequences

Hard

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 1

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 2

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 3

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.

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

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 |

[ {"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} ]