Solution


Approach: Using self join and abs()[Accepted]

Intuition

There is only one table in this problem, so we probably need to use self join for this relative complex problem.

Algorithm

First, let's see what we have after joining this table with itself.

Note: The result of join two tables is the Cartesian product of these two tables.

select a.seat_id, a.free, b.seat_id, b.free
from cinema a join cinema b;
seat_idfreeseat_idfree
1111
2011
3111
4111
5111
1120
2020
3120
4120
5120
1131
2031
3131
4131
5131
1141
2041
3141
4141
5141
1151
2051
3151
4151
5151

To find the consecutive available seats, the value in the a.seat_id should be more(or less) than the value b.seat_id, and both of them should be free.

select a.seat_id, a.free, b.seat_id, b.free
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true;
seat_idfreeseat_idfree
4131
3141
5141
4151

At last, choose the concerned column seat_id, and display the result ordered by seat_id.

Note: You may notice that the seat with seat_id '4' appears twice in this table. This is because seat '4' next to '3' and also next to '5'. So we need to use distinct to filter the duplicated records.

MySQL

select distinct a.seat_id
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true
order by a.seat_id;
SELECT distinct seat_id
FROM cinema
WHERE free = 1 AND
(seat_id - 1 in (select seat_id FROM cinema WHERE free = 1) OR
seat_id + 1 in (select seat_id FROM cinema WHERE free = 1))
ORDER BY seat_id;