Consecutive Available Seats 11-10-22

link ----  https://www.codingninjas.com/codestudio/problems/consecutive-available-seats_2111953?topList=top-100-sql-problems&leftPanelTab=0

Problem Statement

 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?

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

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

| seat_id |
|---------|
| 3       |
| 4       |
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.



---------------------- solution 1 -----------------------------

select c2.seat_id from cinema c1
join cinema c2 
on c1.seat_id +1=c2.seat_id
and c2.free = '1'



------------------- solution 2 -------------------------------------


select seat_id from (select seat_id ,free,
lead(free) over(order by seat_id,1,false)  led
from cinema) as x
where x.free = x.led