Consecutive Available Seats - LeetCode
Table: Cinema +————-+——+ | Column Name | Type | +————-+——+ | seat_id | int | | free | bool | +————-+——+
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.
Write an SQL query to report 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 query 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 | +———+
- code
select
distinct a.seat_id
from Cinema a join Cinema b on abs(a.seat_id - b.seat_id) = 1
where a.free = '1' and b.free = '1'
-- and a.free = '1' and b.free = '1'
order by a.seat_id