UST Global SQL Interview Question - LAG & LEAD Functions

One of the SQL questions recently asked in UST Global interview.
Given us cinemas_tbl, we need to display available seats which are in squence.
In order to solve this questions, we used LAG and LEAD Functions. You will understand how lag and lead functions works in this video.
Let us first create cinemas table
create table cinema_tbl (seat_id int, free int)
Insert the records
insert into cinema_tbl values (1,1),(2,0),(3,1),(4,0),(5,1),(6,1),(7,1),(8,0),(9,1),(10,1)
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@shashank_1180]

solution 2 using advanced window functions
with cte as(
select *
,sum(free)over(order by seat_id rows between 1 preceding and current row ) as prev_row
,sum(free)over(order by seat_id rows between current row and 1 following ) as next_row
from #cinema_tbl
)
select seat_id
from cte
where prev_row=2 or next_row=2

[@CloudChallengers]

Interesting. Thanks for posting alternative approach Shashank.

[@jhonsen9842]

This ans should be Pinned as Best and Eloquent and very intuitive.

[@shashank_1180]

@@jhonsen9842 Thank you

[@theinsightminer08]

SELECT
seat_id
FROM
(SELECT
seat_id,
free,
LAG(free) OVER(ORDER BY seat_id) AS previous_seat,
LEAD(free) OVER(ORDER BY seat_id) AS next_seat
FROM cinema_tbl) as seats
WHERE free = 1 AND (previous_seat = 1 OR next_seat = 1)
ORDER BY seat_id;

[@chandanpatra1053]

Good question. Bring such type of questions.

[@CloudChallengers]

Sure Chandan, I will keep posting such kind of interview questions.

[@iamram436]

with cte as(select seat_id from cinempl where free=1),
cte2 as (select *, lag(seat_id,1,seat_id) over(order by seat_id) as ld,
lead(seat_id,1,seat_id) over(order by seat_id) as lg from cte)
select seat_id from cte2 where seat_id-ld=1 or lg-seat_id=1

[@CloudChallengers]

Thanks for posting different approach ram.

[@adityavamsi12]

with cte as
(select *,
lag(free, 1, 0) over() as prev,
lead(free, 1, 0) over() as nxt
from cinema_tbl)
select seat_id from cte
where free = 1 and (prev = 1 or nxt = 1);

[@anirbanbiswas7624]

THIS CAN BE A DYNAMIC APPROACH TOO
with cte as (select *,row_number() over(order by seat_id) as rn,
seat_id - row_number() over(order by seat_id) as diff
from cinema_tbl where free0),
t2 as (
select *, count(diff) over(partition by diff) as cnt from cte)
select seat_id from t2 where cnt>1

[@maheshnagisetty4485]

select seat_id from
(select *, lag(free) over(order by seat_id) as previous,lead(free) over(order by seat_id) as next_
from cinema_tbl
) as a
where (free + previous>=2) or (free + next_>=2)

[@dibakarmandal2148]

Small effort
with step1 AS
(
select *
, LAG(free,1) over(order by seat_id) as previous_seat
, LEAD(free,1) over(order by seat_id) as next_seat
from ust_cinema_tbl
)
, step2 AS
(
select * from step1 where free !=0
)
select seat_id from step2 where previous_seat = 1
UNION
select seat_id from step2 where next_seat = 1;

[@CloudChallengers]

Thanks for sharing different approach Dibakar. Keep sharing different approach for upcoming videos as well.

[@ChaitanyaKariya-x4q]

select * from (select
case when free = 1 and (lead(free) over(order by seat_id) = 1 or lag(free) over(order by seat_id) = 1) then seat_id end as seat_idd
from cinema_tbl) x
where seat_idd is not null

[@sujanthapa2856]

i changed table name and here is solution. your solution was also good
with recursive cte as(
select *, case when free = 1 and lead(free) over (order by seat_id) = 1 then seat_id else null end as s_id
from cinema
union
select *, case when free = 1 and lag(free) over (order by seat_id) = 1 then seat_id else null end as s_id
from cinema
)
select s_id from cte
where s_id is not null
order by 1

[@CloudChallengers]

@sujanthapa2856, Thanks for you comments

[@lakshmanlee3579]

Code:
with cte as (select *,
row_number() over (order by seat_id) rn
from cinema_tbl
where free = 1),
cte2 as (
select seat_id,free,(seat_id - rn ) num
from cte),
final as (
select seat_id,
count(num) over (partition by num) cnt
from cte2)
select seat_id
from final
where cnt > 1

[@srushtiOm]

Slight change in the solution suggested by you -
with cte as
(select *, lag(free,1,0) over(order by seat_id) as previous_seat,
lead(free,1,0) over(order by seat_id) as next_seat
from cinema_tbl)
select seat_id from cte where free * previous_seat = 1 or next_seat * free = 1

[@sohinibanerjee9617]

Another solution:
;with cter as
(select * ,coalesce(lag(free) over (order by seatid),0) as Prevseat,
coalesce(lead(free) over (order by seatid),0) as Nxtseat from cinema )
select SeatId from cter where (free=1 and Prevseat=1) or (free=1 and Nxtseat=1)

[@rohithr9122]

select seat_id from (
select *,lag(free,1)over(order by seat_id) prvd ,
lead(free,1)over(order by seat_id)nextd from cinema_tbl ) t1
where free = prvd or free = nextd

[@CloudChallengers]

Thanks for posting different approach Rohit. Keep posting alternative approaches for upcoming videos as well.

[@shashank_1180]

solution 1:
with cte as(
select *
,LAG(free,1,0)over(order by seat_id) as prev_row
,LEAD(free,1,0)over(order by seat_id) as next_row
from #cinema_tbl
)
select seat_id
from cte
where (free + prev_row>=2) or (free + next_row>=2)

[@prajju8114]

with cte_seat as
(
select seat_id,free,lag(free) over(order by seat_id) as 'prev_id', lead(free) over(order by seat_id) as 'next_id' from cinema_tbl
)
select seat_id from cte_seat where prev_id!=free and next_id=free or prev_id=free and next_id=free or prev_id=free and next_id!=free or prev_id=free

[@asthatiwari2011]

how can ffind 3 consecutive seat

[@GowthamR-ro2pt]

Hi,I have a different approach easy than this to get the same output, viewers can also use this :
with cte as (select seat_id,free, ROW_NUMBER () over (order by seat_id) rn , (seat_id - ROW_NUMBER () over (order by seat_id)) rn2 from cinema_tbl
where free = 1)
select seat_id from cte
where rn2 >= 2

[@CloudChallengers]

Thanks for posting different approach Gowtham. Keep posting alternative approaches for upcoming videos as well.

[@GowthamR-ro2pt]

Sure 😁👍🏻

[@chandanpatra1053]

please solve this question and make a video on it
create table tbl (sales_date date , sales_amt int);
insert into tbl values ('2023-01-01',30);
insert into tbl values ('2023-01-02',48);
insert into tbl values ('2023-01-03',30);
insert into tbl values ('2023-01-04',29);
insert into tbl values ('2023-01-05',57);
insert into tbl values ('2023-01-06',65);
insert into tbl values ('2023-01-07',36);
insert into tbl values ('2023-01-08',57);
insert into tbl values ('2023-01-09',65);
insert into tbl values ('2023-01-10',31);
Question is you have to find all the rows where the sales amount is present in previous 3 consecutive rows and assign it as 1
For eg. sales amount 57 having sales_date 2023-01-08 is present in sales_date 2023-01-05. so it should be assign as 1 by making a new column result.
Output should be
sales_date Sales_amt result
1/1/2023 29 0
1/2/2023 40 0
1/3/2023 36 0
1/4/2023 29 1
1/5/2023 57 0
1/6/2023 65 0
1/7/2023 36 0
1/8/2023 57 1
1/9/2023 65 1
1/10/2023 31 0
solve in such a way that it will be generic. If question is asked about present in previous 10 rows .so try to solve it in such a manner it will be easier to understand.

[@CloudChallengers]

Thanks for posting the SQL Interview question here Chandan. I will try to post a video on this question soon.

[@gouthamstar6558]

with cte as (
select seat_id, lag(free) over(order by seat_id) as prev_seat, free as current_seat, lead(free) over(order by seat_id) as next_seat
from cinemas_tbl
)
, cte2 as (
--select * from cte
select seat_id,
case when prev_seat=current_seat or current_seat=next_seat then 1 else 0 end as result
from cte
)
select seat_id from cte2
where result = '1';

[@CloudChallengers]

Yes, that works. Thanks for posting the different approach Goutham

[@josejoel9346]

Select seat_id from cinema_tbl where free = 1 order by seat_id
Sir this query is right or wrong.

[@CloudChallengers]

Your query don't give expected output Jose. The expected output should have available CONSECUTIVE seats only, not all the free seats.

[@97_sumeetbhosale63]

Is it for Fresher role or expericed role Question?

[@CloudChallengers]

This question is asked for experienced candidate with 4+ years of experience in data analytics background.

[@harshitsalecha221]

WITH cte1 AS (SELECT * FROM cinema_tbl
WHERE free=1)
SELECT seat_id FROM (SELECT seat_id,LEAD(seat_id) OVER(ORDER BY seat_id)-seat_id as diff1,seat_id-LAG(seat_id) OVER(ORDER BY seat_id) as diff2 FROM cte1) as a
WHERE diff1=1 OR diff2=1;

[@ajaykrishnanj5633]

with cte as(
select *,LAG(seat_id,1,0) over(order by seat_id) as next_seat,
lead(seat_id,1,0) over(order by seat_id) as preseat from cinema_tbl
where free= 1),
cte2 as(
select * ,case when next_seat=seat_id-1 or preseat=seat_id+1
then '1' else '0' end as rn from cte where next_seat!=0)
select seat_id from cte2
where rn!= 0

[@CloudChallengers]

Awesome Ajay. It works, Thanks for posting the alternative approach.

[@bibekrawat2284]

SELECT
tt.seat_id
from
(
select
t.seat_id,
t."result"
,
count(t."result") over (PARTITION by t."result"
order by
t."result") "cnt"
from
(
select
seat_id,
"free",
row_number() over (
order by seat_id asc) "rn",
seat_id-row_number() over (
order by seat_id asc) "result"
from
cinema_tbl
WHERE
free = 1)t)tt
where
cnt>1;

[@CloudChallengers]

Thanks for sharing different approach Bibek. Keep sharing different approach for upcoming videos as well.