Consecutive Available Seats

Problem

Table: Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+
In SQL, seat_id is an auto-increment primary key 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.

Solution

The problem Consecutive Available Seats can be solved using keywords INNER JOIN, ORDER BY and DISTINCT along with the function ABS.

Implementation

# Write your MySQL query statement below

SELECT DISTINCT c1.seat_id AS seat_id
FROM
  cinema AS c1
  INNER JOIN cinema AS c2 ON abs(c1.seat_id - c2.seat_id) = 1 AND c1.free AND c2.free
ORDER BY c1.seat_id;