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.
sql
# Write your MySQL query statement below SELECT DISTINCT a.seat_id FROM Cinema AS a JOIN Cinema AS b ON ABS(a.seat_id - b.seat_id) = 1 AND a.free AND b.free ORDER BY 1;(code-box)
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.
sql
# Write your MySQL query statement below WITH T AS ( SELECT seat_id, (free + (LAG(free) OVER (ORDER BY seat_id))) AS a, (free + (LEAD(free) OVER (ORDER BY seat_id))) AS b FROM Cinema ) SELECT seat_id FROM T WHERE a = 2 OR b = 2;(code-box)
Solution 3
sql
# Write your MySQL query statement below WITH T AS ( SELECT *, SUM(free = 1) OVER ( ORDER BY seat_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS cnt FROM Cinema ) SELECT seat_id FROM T WHERE free = 1 AND cnt > 1 ORDER BY 1;(code-box)
