UST Global SQL Interview Question - LAG & LEAD Functions

[@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

[@chandanpatra1053]

Good question. Bring such type of questions.

[@CloudChallengers]

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

[@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)

[@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

[@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

[@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.

[@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)

[@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);

[@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.

[@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

[@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.

[@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;

[@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.

[@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)

[@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 😁👍🏻

[@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.

[@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

[@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.

[@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.