Consecutive Available Seats Problem


Description

LeetCode Problem 603.

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

1
2
3
4
5
6
7
| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |

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

1
2
3
4
5
| seat_id |
|---------|
| 3       |
| 4       |
| 5       |

Note:

  • The seat_id is an auto increment int, and free is bool (‘1’ means free, and ‘0’ means occupied.).
  • Consecutive available seats are more than 2(inclusive) seats consecutively available.


MySQL Solution

1
2
3
4
5
6
select c1.seat_id
from cinema c1, cinema c2
where ((c1.seat_id = c2.seat_id+1) or
       (c1.seat_id = c2.seat_id-1)) and 
    (c1.free = 1) and (c2.free = 1)
group by c1.seat_id




Related Posts

Human Traffic Of Stadium Problem

LeetCode 601. Write an SQL query to display the records...

Friend Requests II: Who Has The Most Friends Problem

LeetCode 602. In social network like Facebook or Twitter, people...

Tree Node Problem

LeetCode 608. Given a table tree, id is identifier of...

Friend Requests I: Overall Acceptance Rate Problem

LeetCode 597. Write an SQL query to find the overall...

Sales Person Problem

LeetCode 607. Given three tables, salesperson, company, orders.

Consecutive Available Seats Problem

LeetCode 603. Several friends at a cinema ticket office would...