problemmediumdatabaseleetcode-3140leetcode 3140leetcode3140

Consecutive Available Seats II

MediumUpdated: Sep 29, 2025
Practice on:
LeetCode

Problem

Table: Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+
seat_id is an auto-increment column for this table.
Each row of this table indicates whether the ith seat is free or not. 1 means free while 0 means occupied.

Write a solution to find the length of longest consecutive sequence of available seats in the cinema.

Note:

  • There will always be at most one longest consecutive sequence.
  • If there are multiple consecutive sequences with the same length , include all of them in the output.

Return the result tableordered by first_seat_id in ascending order.

The result format is in the following example.

Examples

Example 1:

Input:
Cinema 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                     |
+-----------------+----------------+-----------------------+
Explanation:
* Longest consecutive sequence of available seats starts from seat 3 and ends at seat 5 with a length of 3.
Output table is ordered by first_seat_id in ascending order.

Solution

Method 1 – Window Functions and Grouping

Intuition

We want to find the length of the longest consecutive sequence of available seats (free = 1). We can use window functions to assign a group number to each consecutive block of free seats, then count the length of each group and select the maximum.

Approach

  1. Use a window function to assign a group number to each consecutive block of free seats. This can be done by subtracting the row number from the seat_id for free seats.
  2. Filter only free seats (free = 1).
  3. Group by the group number and count the number of seats in each group.
  4. Find the maximum length among all groups.
  5. If there are multiple groups with the same maximum length, include all of them in the output.

Code

MySQL
WITH free_seats AS (
  SELECT seat_id, free,
         seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
  FROM Cinema
  WHERE free = 1
)
SELECT COUNT(*) AS length
FROM free_seats
GROUP BY grp
HAVING COUNT(*) = (
  SELECT MAX(cnt) FROM (
    SELECT COUNT(*) AS cnt FROM free_seats GROUP BY grp
  ) t
);
PostgreSQL
WITH free_seats AS (
  SELECT seat_id, free,
         seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
  FROM Cinema
  WHERE free = 1
)
SELECT COUNT(*) AS length
FROM free_seats
GROUP BY grp
HAVING COUNT(*) = (
  SELECT MAX(cnt) FROM (
    SELECT COUNT(*) AS cnt FROM free_seats GROUP BY grp
  ) t
);
Python (Pandas)
def longest_consecutive_free_seats(cinema: 'pd.DataFrame') -> 'pd.DataFrame':
    df = cinema[cinema['free'] == 1].copy()
    df['grp'] = df['seat_id'] - range(len(df))
    group_sizes = df.groupby('grp').size()
    max_len = group_sizes.max() if not group_sizes.empty else 0
    result = group_sizes[group_sizes == max_len].reset_index(drop=True).to_frame('length')
    return result[['length']]

Complexity

  • ⏰ Time complexity: O(n), where n is the number of seats. Each seat is processed once.
  • 🧺 Space complexity: O(n), for storing group information and results.

Comments