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.

Difficulty Level – Easy

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.