Consecutive Available Seats II - Problem
You have a table Cinema with information about cinema seats and their availability status.
Table: Cinema
seat_id(int): Auto-increment column representing the seat identifierfree(bool): Indicates whether the seat is available (1 = free, 0 = occupied)
Task: Find the length of the longest consecutive sequence of available seats in the cinema.
Important notes:
- There will always be at most one longest consecutive sequence
- If there are multiple consecutive sequences with the same maximum length, include all of them
- Return results ordered by
first_seat_idin ascending order
Output columns:
first_seat_id: Starting seat of the consecutive sequencelast_seat_id: Ending seat of the consecutive sequenceconsecutive_seats_len: Length of the consecutive sequence
Table Schema
Cinema
| Column Name | Type | Description |
|---|---|---|
seat_id
PK
|
int | Auto-increment seat identifier |
free
|
bool | Seat availability (1 = free, 0 = occupied) |
Primary Key: seat_id
Note: Each row represents one cinema seat and its availability status
Input & Output
Example 1 — Basic Consecutive Sequence
Input Table:
| seat_id | free |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
Output:
| first_seat_id | last_seat_id | consecutive_seats_len |
|---|---|---|
| 3 | 5 | 3 |
💡 Note:
The longest consecutive sequence of available seats starts from seat 3 and ends at seat 5 with a length of 3. Seat 1 is available but isolated, while seat 2 is occupied, breaking the sequence.
Example 2 — Multiple Equal Length Sequences
Input Table:
| seat_id | free |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |
| 5 | 1 |
| 6 | 0 |
Output:
| first_seat_id | last_seat_id | consecutive_seats_len |
|---|---|---|
| 1 | 2 | 2 |
| 4 | 5 | 2 |
💡 Note:
Two consecutive sequences have the same maximum length of 2: seats 1-2 and seats 4-5. Both sequences are included in the output, ordered by first_seat_id.
Example 3 — All Seats Occupied
Input Table:
| seat_id | free |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
Output:
| first_seat_id | last_seat_id | consecutive_seats_len |
|---|
💡 Note:
No seats are available (all free = 0), so there are no consecutive sequences to return. The result is an empty table.
Constraints
-
1 ≤ seat_id ≤ 50 -
freeis either0or1 - There will always be at most one longest consecutive sequence
Visualization
Tap to expand
Understanding the Visualization
1
Filter
Extract only available seats (free = 1)
2
Group
Identify consecutive sequences using window functions
3
Measure
Calculate length and find maximum sequences
Key Takeaway
🎯 Key Insight: Use seat_id - ROW_NUMBER() to group consecutive sequences, then aggregate to find maximum lengths
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code