Consecutive Available Seats II - Practice Coding Problems

Consecutive Available Seats II - Problem

Database Medium

🎬 Cinema Seat Optimization

Imagine you're building a movie theater booking system! You have a cinema with seats arranged in a single row, where each seat can either be available (free = 1) or occupied (free = 0).

Your task is to find the longest consecutive sequence of available seats. This is crucial for accommodating large groups who want to sit together!

Goal: Find all sequences with the maximum length of consecutive available seats.

Input: A table Cinema with columns:

seat_id (int): Sequential seat number
free (bool): 1 = available, 0 = occupied

Output: For each longest sequence, return:

first_seat_id: Starting seat of the sequence
last_seat_id: Ending seat of the sequence
consecutive_seats_len: Length of the sequence

Example: Seats [1,2,3,4,5] with availability [1,0,1,1,1] → longest sequence is seats 3-5 (length 3)

Input & Output

example_1.sql — Basic Case

$ 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 | +-----------------+----------------+-----------------------+

💡 Note: Seats 1 is available alone (length 1), while seats 3,4,5 form a consecutive sequence (length 3). The maximum length is 3, so we return the sequence starting at seat 3 and ending at seat 5.

example_2.sql — Multiple Equal Sequences

$ Input: Cinema table: +---------+------+ | seat_id | free | +---------+------+ | 1 | 1 | | 2 | 1 | | 3 | 0 | | 4 | 1 | | 5 | 1 | | 6 | 0 | | 7 | 1 | +---------+------+

› Output: +-----------------+----------------+-----------------------+ | first_seat_id | last_seat_id | consecutive_seats_len | +-----------------+----------------+-----------------------+ | 1 | 2 | 2 | | 4 | 5 | 2 | +-----------------+----------------+-----------------------+

💡 Note: There are three available sequences: seats 1-2 (length 2), seats 4-5 (length 2), and seat 7 (length 1). The maximum length is 2, so we return both sequences with length 2, ordered by first_seat_id.

example_3.sql — All Seats Available

$ Input: Cinema table: +---------+------+ | seat_id | free | +---------+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +---------+------+

› Output: +-----------------+----------------+-----------------------+ | first_seat_id | last_seat_id | consecutive_seats_len | +-----------------+----------------+-----------------------+ | 1 | 4 | 4 | +-----------------+----------------+-----------------------+

💡 Note: All seats are available and consecutive, forming one sequence from seat 1 to seat 4 with length 4. This is the only sequence and also the maximum length.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with ORDER BY for ROW_NUMBER(), where n is the number of seats

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for grouping and window function results

⚡ Linearithmic Space

Constraints

1 ≤ seat_id ≤ 10⁵
free is either 0 (occupied) or 1 (available)
seat_id values are unique and auto-incrementing
There will always be at most one longest consecutive sequence length (but multiple sequences can have this same maximum length)

Asked in

The optimal solution uses the ROW_NUMBER() window function technique to efficiently group consecutive available seats. By calculating seat_id - ROW_NUMBER() for each available seat, consecutive seats will have the same group key. This allows us to find the longest consecutive sequence in O(n log n) time with a single, elegant SQL query using Common Table Expressions (CTEs).

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Scans)	O(n²)	O(1)	Check every possible starting seat and count consecutive available seats from there
Window Technique with ROW_NUMBER (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() to identify consecutive groups in a single efficient pass

Brute Force (Multiple Scans) — Algorithm Steps

Find all available seats
For each available seat, try it as a starting point
Count consecutive available seats from that starting point
Track the maximum length and corresponding sequences
Return all sequences with maximum length

Visualization

Tap to expand

Step-by-Step Walkthrough

Identify Available Seats

Find all seats where free = 1

Check Each as Start

For each available seat, try it as sequence start

Count Consecutive

Count how many consecutive available seats follow

Track Maximum

Keep track of the longest sequence found

Code -

solution.c — C

// SQL solution using brute force approach
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int first_seat;
    int last_seat;
    int length;
} SeatSequence;

int findConsecutiveSeats(SeatSequence** result) {
    // SQL query for brute force approach
    char* sql = ""
        "WITH all_starts AS (\n"
        "    SELECT seat_id as start_id\n"
        "    FROM Cinema\n"
        "    WHERE free = 1\n"
        "      AND (seat_id = (SELECT MIN(seat_id) FROM Cinema WHERE free = 1)\n"
        "           OR (SELECT COALESCE(free, 0) FROM Cinema \n"
        "               WHERE seat_id = Cinema.seat_id - 1) = 0)\n"
        "),\n"
        "sequence_lengths AS (\n"
        "    SELECT start_id,\n"
        "           (SELECT COUNT(*)\n"
        "            FROM Cinema c\n"
        "            WHERE c.seat_id >= start_id\n"
        "              AND c.seat_id < COALESCE(\n"
        "                  (SELECT MIN(c2.seat_id)\n"
        "                   FROM Cinema c2\n"
        "                   WHERE c2.seat_id > start_id AND c2.free = 0),\n"
        "                  (SELECT MAX(seat_id) + 1 FROM Cinema)\n"
        "              )\n"
        "              AND c.free = 1) as seq_len\n"
        "    FROM all_starts\n"
        ")\n"
        "SELECT start_id as first_seat_id,\n"
        "       start_id + seq_len - 1 as last_seat_id,\n"
        "       seq_len as consecutive_seats_len\n"
        "FROM sequence_lengths\n"
        "WHERE seq_len = (SELECT MAX(seq_len) FROM sequence_lengths)\n"
        "ORDER BY start_id;";
    
    // Simulate brute force logic in C
    int cinemaData[][2] = {{1,1}, {2,0}, {3,1}, {4,1}, {5,1}};
    int dataSize = 5;
    int maxLen = 0;
    int sequenceCount = 0;
    
    // First pass: find maximum length
    for (int i = 0; i < dataSize; i++) {
        if (cinemaData[i][1] == 0) continue;
        
        int currentLen = 0;
        for (int j = i; j < dataSize && cinemaData[j][1] == 1; j++) {
            currentLen++;
        }
        
        if (currentLen > maxLen) {
            maxLen = currentLen;
            sequenceCount = 1;
        } else if (currentLen == maxLen) {
            sequenceCount++;
        }
    }
    
    // Allocate memory for results
    *result = (SeatSequence*)malloc(sequenceCount * sizeof(SeatSequence));
    int resultIndex = 0;
    
    // Second pass: collect all sequences with maximum length
    for (int i = 0; i < dataSize; i++) {
        if (cinemaData[i][1] == 0) continue;
        
        int currentLen = 0;
        for (int j = i; j < dataSize && cinemaData[j][1] == 1; j++) {
            currentLen++;
        }
        
        if (currentLen == maxLen) {
            (*result)[resultIndex].first_seat = cinemaData[i][0];
            (*result)[resultIndex].last_seat = cinemaData[i][0] + currentLen - 1;
            (*result)[resultIndex].length = currentLen;
            resultIndex++;
        }
    }
    
    return sequenceCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n seats, we potentially scan n seats to find consecutive sequences

⚠ Quadratic Growth

Space Complexity

O(1)

Only storing the maximum length and current sequence information

✓ Linear Space

Constraints

1 ≤ seat_id ≤ 10⁵
free is either 0 (occupied) or 1 (available)
seat_id values are unique and auto-incrementing
There will always be at most one longest consecutive sequence length (but multiple sequences can have this same maximum length)

25.0K Views

Medium Frequency

~15 min Avg. Time

850 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

🎬 Cinema Seat Optimization

Input & Output

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Scans) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler