603. Consecutive Available Seats π
Description
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.
Find all the consecutive available seats in the cinema.
Return the result table ordered by seat_id
in ascending order.
The test cases are generated so that more than two seats are consecutively available.
The result format is in the following example.
Example 1:
Input: Cinema table: +---------+------+ | seat_id | free | +---------+------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | +---------+------+ Output: +---------+ | seat_id | +---------+ | 3 | | 4 | | 5 | +---------+
Solutions
Solution 1: Self-Join
We can use a self-join to join the Seat
table with itself, and then filter out the records where the id
of the left seat is equal to the id
of the right seat minus $1$, and where both seats are empty.
1 2 3 4 5 6 |
|
Solution 2: Window Function
We can use the LAG
and LEAD
functions (or SUM() OVER(ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
) to obtain the information of adjacent seats, and then filter out the consecutive empty seats and sort them in a unique way.
1 2 3 4 5 6 7 8 9 10 11 12 |
|
Solution 3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|