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.

Rating: 1 out of 5.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s