Consecutive Available Seats - Problem

You are given a table Cinema that contains information about cinema seats and their availability status.

Table: Cinema

Column NameType
seat_idint
freebool

seat_id is an auto-increment column for this table. Each row indicates whether the seat is available or occupied.

  • 1 means the seat is free (available)
  • 0 means the seat is occupied

Task: Find all consecutive available seats in the cinema. Return the result ordered by seat_id in ascending order.

Note: The test cases guarantee that more than two seats are consecutively available.

Table Schema

Cinema
Column Name Type Description
seat_id PK int Auto-increment primary key representing seat number
free bool 1 if seat is available, 0 if occupied
Primary Key: seat_id
Note: Seats are numbered sequentially, and we need to find groups of consecutive available seats

Input & Output

Example 1 — Mixed Available and Occupied Seats
Input Table:
seat_id free
1 1
2 1
3 0
4 1
5 1
Output:
seat_id
1
2
4
5
💡 Note:

Seats 1 and 2 are both free and consecutive. Seat 3 is occupied, so it breaks the sequence. Seats 4 and 5 are both free and consecutive. Therefore, all seats except seat 3 are part of consecutive available groups.
Example 2 — Long Consecutive Sequence
Input Table:
seat_id free
1 0
2 1
3 1
4 1
5 0
Output:
seat_id
2
3
4
💡 Note:

Seats 2, 3, and 4 form one continuous block of available seats. Seats 1 and 5 are occupied, so they don't appear in the result. All three consecutive seats (2, 3, 4) are returned.
Example 3 — Multiple Groups
Input Table:
seat_id free
1 1
2 1
3 0
4 0
6 1
7 1
8 1
Output:
seat_id
1
2
6
7
8
💡 Note:

Two separate groups of consecutive seats: seats 1-2 form one group, and seats 6-7-8 form another group. Seats 3 and 4 are occupied. Note that seat 5 is missing from the table, creating a gap between the groups.

Constraints

  • 1 ≤ seat_id ≤ 50
  • free is either 0 or 1
  • More than two seats are consecutively available

Visualization

Tap to expand
Finding Consecutive Available Cinema SeatsCinema Tableseat_idfree1121304151SELF JOINFind AdjacentFree SeatsConsecutive Seatsseat_id1245Group 1: Seats 1,2Seat 3: OccupiedGroup 2: Seats 4,5AvailableOccupied
Understanding the Visualization
1
Input
Cinema table with seat_id and availability status
2
Self-Join
Compare each seat with adjacent seats
3
Filter
Find seats that are free and have free neighbors
Key Takeaway
🎯 Key Insight: Use self-join to compare adjacent rows when finding consecutive patterns in SQL

Related Problems

Asked in
Amazon 12 Microsoft 8
28.0K Views
Medium Frequency
~12 min Avg. Time
890 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