#SQL_Challenge_9

Here, is the Question statement.

Write a SQL query to find the seat numbers where 4 consecutive seats are booked and non booked in a given row.

Here, is the input table :

Input Table

CREATE TABLE seats (
  seat_number VARCHAR(3) NOT NULL,
  booked_status TINYINT(1) NOT NULL,
  PRIMARY KEY (seat_number)
);
INSERT INTO seats (seat_number, booked_status)
VALUES
('A1', 0),
('A2', 1),
('A3', 0),
('A4', 1),
('A5', 0),
('A6', 1),
('A7', 1),
('A8', 1),
('A9', 1),
('A10', 1),
('B1', 0),
('B2', 0),
('B3', 0),
('B4', 0),
('B5', 0),
('B6', 0),
('B7', 1),
('B8', 1),
('B9', 0),
('B10', 0);
-- select * from seats;
-- DESCRIBE table seats ;
-- SHOW COLUMNS FROM seats; 
-- Checking 4 consecutive booked seats
with cte as (
select *, substring(seat_number, 1,1) as row_id, 
substring(seat_number,2)+0 as seat_id

from seats 
order by substring(seat_number, 1,1) ,substring(seat_number,2)+0  )

, cte2 as (
select * , 
min(booked_status) over(partition by row_id order by seat_id rows between current row and 3 following) as min_flg_booked,
max(booked_status) over(partition by row_id order by seat_id rows between current row and 3 following) as max_flg_non_booked,
count(booked_status) over(partition by row_id order by seat_id rows between current row and 3 following) as count_flg
from cte ) 

, cte3 as (
select *
from cte2
where (min_flg_booked=1 and count_flg=4) or (max_flg_non_booked=0 and count_flg=4) )

select distinct cte2.seat_number,  cte2.booked_status from cte3 join cte2 
on cte3.row_id = cte2.row_id and (cte2.seat_id BETWEEN cte3.seat_id and cte3.seat_id+3)  ;



-- Alternate solution 

with cte as (
select * from (
select *, substring(seat_number, 1,1) as seat_row,
substring(seat_number, 2)+0   as seat_no
from seats ) x 
order by seat_row, seat_no  ) 


select seat_number, booked_status from (
select * from (
select *, count(*) over(partition by seat_row, flg) as cnt from (
select *, (seat_no - row_number() over(partition by seat_row  order by seat_no)) as flg  
from cte 
where booked_status = 1 ) y ) z 
where cnt >=4 

union all 

select * from (
select *, count(*) over(partition by seat_row, flg) as cnt from (
select *, (seat_no - row_number() over(partition by seat_row  order by seat_no)) as flg  
from cte 
where booked_status = 0 ) y ) z 
where cnt >=4 ) x

Output:

Output snippet

This query is run for multiple edge cases and works well in all cases.

You can checkout my answer and run edges cases in this compiler.

Learnings:

Exposure to the use cases of window frame clause which allows you to perform calculations over of subset of rows which you specify within the partition.

This problem can further be extended to scenarios such as identifying seat_ids that have been labeled as booked for a minimum of three consecutive instances.

Thank you!