# Amazon SQL interview questions – Leetcode 603 – Consecutive Available Seats

### Problem Description –

Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seat orders by the seat_id using the following `cinema` table?

Your query should return the following result for the sample case above.

### Solution –

``````SELECT
DISTINCT t1.seat_id
FROM cinema AS t1 JOIN cinema AS t2
ON abs(t1.seat_id - t2.seat_id) = 1
AND t1.free = 1 AND t2.free = 1
ORDER BY 1 ``````

To solve this problem, first, we need to do join the cinema table to itself using a self-join. And to find the consecutive seats we have to use `abs(t1.seat_id - t2.seat_id) = 1` because the value of the t1.seat_id will be one more or less than the value of the t2.seat_id and we also have to make sure that the seat is available by using another condition for joining the table as` t1.free = 1 AND t2.free = 1`. This will give us the following output.

``````SELECT t1.*, t2.*
FROM cinema AS t1 JOIN cinema AS t2
ON abs(t1.seat_id - t2.seat_id) = 1
AND t1.free = 1 AND t2.free = 1
ORDER BY 1 ``````

One thing to notice is that we have two values for 4 in the seat_id column because seat number 4 is next to 3 as well as next seat number 5. So, we need to use the DISTINCT keyword in the select statement in the final query.

Rating: 1 out of 5.