leetcode - 603. Consecutive Available Seats

난이도 : Easy

 

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

 

join을 활용한 방법

SELECT DISTINCT t1.seat_id
FROM Cinema t1
     JOIN Cinema t2 ON ABS(t1.seat_id - t2.seat_id) = 1
                    AND t1.free = true
                    AND t2.free = true
ORDER BY seat_id

Accepted (70.03%)

 

 

윈도우 함수를 활용한 방법

WITH sub AS (
            SELECT LAG(seat_id) OVER (ORDER BY seat_id) last_seat_id
                 , seat_id
                 , LEAD(seat_id) OVER (ORDER BY seat_id) next_seat_id
                 , free
            FROM Cinema
            WHERE free = 1
            )
SELECT seat_id
FROM sub
WHERE seat_id - 1 = last_seat_id 
OR seat_id + 1 = next_seat_id
ORDER BY seat_id

Accepted (74.74%)