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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@asthatiwari2011]

how can ffind 3 consecutive seat

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

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

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

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

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