Thanks a ton Ankit . I have done in little more extensive way but your approach is something more catchy (method#3) -- ===== Method 1 ========== with cte as (select seat_no, lag(seat_no) over ( order by seat_no) as prev_seat_no, lead(seat_no) over ( order by seat_no) as next_seat_no, is_empty as current_seat, lag(is_empty) over ( order by seat_no) as prev_seat, lead(is_empty) over ( order by seat_no) as next_seat from bookmyshow ), cte2 as ( Select prev_seat_no,seat_no,next_seat_no from cte where prev_seat='Y' and current_seat='Y' and next_seat='Y') // Since the requirement is in one single column but unknowingly this too be question Select distinct prev_seat_no as seat_no from cte2 union Select distinct seat_no as seat_no from cte2 union Select distinct next_seat_no as seat_no from cte2 -- ===== Method 2 ========== with method2 as ( Select * , sum( case when is_empty='Y' then 1 else 0 end) over ( order by seat_no rows between 2 preceding and current row) as prev_row2, sum( case when is_empty='Y' then 1 else 0 end) over ( order by seat_no rows between current row and 2 following ) as next_row2, sum( case when is_empty='Y' then 1 else 0 end) over ( order by seat_no rows between 1 preceding and 1 following ) as current_row from bookmyshow) Select seat_no from method2 where 3 in ( prev_row2,current_row,next_row2)
@ankitbansal62 жыл бұрын
Thanks for posting. Great effort 😊
@lucifieramit12 жыл бұрын
Solved toe leet code problems #180 and #601 using the Lead,Lag approach as mentioned in the video . Thanks all your videos are very helpful if one wants to learn advanced SQL
@ankitbansal62 жыл бұрын
Awesome 👍
@nishanthkumar4743 Жыл бұрын
I believe out of 3 solutions, 3rd solution can be applied to other scenarios like 4 or more consecutives with very minimal code changes. Good to know other solutions as well.
@Datapassenger_prashant2 ай бұрын
next level of concepts clearing..thank you so much sir.. maja aa gaya ye video dekh kar..
@ankitbansal62 ай бұрын
Maze aana bahut important hai 😍
@vijaypalmanit2 жыл бұрын
Had I watched your videos early I could have cleared Amazon interview, I thought no one teaches sql so well as all the videos were related to coding on youtube.
@ankitbansal62 жыл бұрын
Better late than never 😊
@karangupta_DE2 жыл бұрын
Hi Ankit, with lead lag, i also checked for the middle row, if the lag and lead is_empty are also the same with cte as ( select seat_no, is_empty, case when is_empty = lead(is_empty)over(order by seat_no) and is_empty = lead(is_empty,2)over(order by seat_no) then 1 when is_empty = lag(is_empty)over(order by seat_no) and is_empty = lead(is_empty)over(order by seat_no) then 1 when is_empty = lag(is_empty)over(order by seat_no) and is_empty = lag(is_empty,2)over(order by seat_no) then 1 else 0 end as empty_flag from bms) select seat_no from cte where empty_flag = 1
@ankitbansal62 жыл бұрын
Problem is it will give consecutive 3 or more non empty chairs also
@PriyaYadav-jh5wj2 жыл бұрын
Yes, works fine. Just add another filter condition : is_empty='Y' in the end
@RajKumar-zw7vtАй бұрын
That's great Ankit. From the 3rd solution, what if the seat_no is not numeric and it contains something like A,B,G? Do we have to generate the row_number twice in this case before and after filter?
@ganeshsinghn61465 ай бұрын
;with cte as ( select *,ROW_NUMBER()over(order by seat_no) as rw,seat_no-ROW_NUMBER()over(order by seat_no) as grp from bms where is_empty='Y' ) select seat_no from cte where grp in( select grp from cte group by grp having count(1)>=3)
@Mysingh97672 жыл бұрын
Thanks for video ankit... 🙏 One request if you get time please make videos on python also.
@ankitbansal62 жыл бұрын
Sure. I will plan the same.
@tupaiadhikari2 жыл бұрын
Too Good Ankit. Enlightening and Easy to Grasp.
@psyche5184 Жыл бұрын
Method 3 is so elegant.
@rahulmehla20143 ай бұрын
my approach : with cte as( select *,seat_no - row_number() over(partition by is_empty) as diff from bms where is_empty = 'Y'), cte2 as( select *,count(1) over(partition by diff) as rpt from cte) select seat_no from cte2 where rpt >= 3;
@himadrivaja30812 ай бұрын
i had a doubt for this approach. Wouldnt a case where non consecutive rpt >3 could be selected ? its not applicable in this case but generally asking
@rahulmehla20142 ай бұрын
@@himadrivaja3081 i didn’t get your question.Why would it be selected?
@abhishekpurohit34422 жыл бұрын
with ctl as (select seat_no,is_empty,is_empty_no, sum(is_empty_no) over(order by seat_no rows between 2 preceding and current row) as case_1, sum(is_empty_no) over(order by seat_no rows between 1 preceding and 1 following) as case_2, sum(is_empty_no) over(order by seat_no rows between current row and 2 following) as case_3 from (select a.*, case when is_empty='Y' then 1 else 0 end as is_empty_no from bms a) as A) select distinct seat_no from ctl where case_1=3 or case_2=3 or case_3=3;
@codespacelk9 ай бұрын
hi Ankit Sir, below solution gives correct answer, can we do like this? select * from (select * , count(is_empty) over (order by seat_no rows between 2 preceding and 2 following) counter from bms) sub where is_empty = 'Y' and counter = 5
@patilnaveenkumarreddy4059 Жыл бұрын
i wish i had watcvhed your videos before attending latham and watkins he asked about window functions and cte too
@komalpawar5124 Жыл бұрын
You are really awesome!!, your way of explaining makes every logic clear!! Keep making such videos so we can learn more from this Videos
@ankitbansal6 Жыл бұрын
Thank you so much 😀
@sakethchandrakolisetty98366 ай бұрын
DAMN, i would have never thought of the second method
@blazerider200 Жыл бұрын
with cte as( select seat_no from bms where is_empty between 'Y' and 'Y' ) ,cte1 as( select *,ROW_NUMBER() OVER (ORDER BY seat_no) as ID from cte) ,cte2 as( select seat_no,(seat_no-Id) as rank from cte1) ,cte3 as( select rank from cte2 group by rank having count(rank)>=3) select seat_no from cte2 c2 inner join cte3 c3 on c2.rank=c3.rank
@prakashkrishnan71322 жыл бұрын
When it comes to oracle , method 1 and 3 would be sufficient. Method 2 has some functional limitations and it would be working fine in SSMS. Over all this is good one dude
@ankitbansal62 жыл бұрын
I think should work in Oracle also method 2. What limitations are there ?
@prakashkrishnan71322 жыл бұрын
@@ankitbansal6 do we have any preceding functions in Oracle. I hope we have some thing like unbound preceding and unbound following in the Nth value function.
@ankitbansal62 жыл бұрын
@@prakashkrishnan7132 Unbound is like running sum. It's there in SQL server also. I think this will work in Oracle.
@pramodgadekar3081 Жыл бұрын
Yes, it works fine in oracle also.
@arthurmorgan9010 Жыл бұрын
Sir my solution: with cte as ( select *,ROW_NUMBER() over(order by seat_no) as se, seat_no -ROW_NUMBER() over(order by seat_no) as so from bms where is_empty = 'Y' ) ,cteone as ( select seat_no,count(so) over(partition by so order by so) as c from cte ) select seat_no from cteone where c>=3
@exanode2 ай бұрын
Great questions as always. I did this using CTE and self joins: WITH empty_neighbors AS ( SELECT a.* FROM bms a JOIN bms b ON a.seat_no - 1 = b.seat_no JOIN bms c ON a.seat_no + 1 = c.seat_no WHERE a.is_empty = 'Y' AND b.is_empty = 'Y' AND c.is_empty = 'Y' ) SELECT DISTINCT m.seat_no, m.is_empty FROM bms m JOIN empty_neighbors n ON m.seat_no = n.seat_no - 1 OR m.seat_no = n.seat_no + 1 OR m.seat_no = n.seat_no ORDER BY m.seat_no;
@rajasharma98864 ай бұрын
with cte as ( select *,sum (case when is_empty='N' then 1 else 0 end ) over (order by seat_no ) as k from bms ),cte2 as (select k,count(seat_no) as cnt from cte where is_empty='y' group by k )--select * from cte2 select seat_no from cte join cte2 on cte.k=cte2.k where is_empty='y' and cnt>=3
@devendrabarasker49875 ай бұрын
thank you so much sir for valuable content ! 🙌
@Aman-lv2eeАй бұрын
with temp as ( Select *, seat_no - row_number()over(order by seat_no) as diff from bms where is_empty = 'Y' ) Select seat_no, count(diff)over(partition by diff) as dk from temp qualify dk >=3;
@anish_bhateja Жыл бұрын
with cte as (select * from bms where is_empty = 'Y'), cte2 as (select *, seat_no - row_number() over(order by seat_no) as cnt from cte order by seat_no) select seat_no from cte2 where cnt in (select cnt from cte2 group by 1 having count(*) >=3);
@dfkgjdflkg7 ай бұрын
as usual, the greatest.
@LiveWithDebasmita2 жыл бұрын
Hi Ankit Sir, Can you please give a suggestion on that? with first_cte as ( select a.* from ( select * , lag(is_empty,1," ") over (order by seat_no) as lag_is_empty , lead(is_empty,1," ") over (order by seat_no) as lead_is_empty from bms ) as a where is_empty = 'Y' and lag_is_empty = 'Y' and lead_is_empty = 'Y' ) select distinct seat_no from ( select a.prev_seat as seat_no from (select seat_no-1 as prev_seat , seat_no , seat_no+1 as next_seat from first_cte) as a inner join bms as b on a.prev_seat = b.seat_no union all select a.seat_no from (select seat_no-1 as prev_seat , seat_no , seat_no+1 as next_seat from first_cte) as a inner join bms as c on a.seat_no = c.seat_no union all select a.next_seat as seat_no from (select seat_no-1 as prev_seat , seat_no , seat_no+1 as next_seat from first_cte) as a inner join bms as d on a.next_seat = d.seat_no ) as a order by a.seat_no ;
@ankitbansal62 жыл бұрын
Looks good. Did you try running it ?
@LiveWithDebasmita2 жыл бұрын
Hi Ankit Sir I ran it. It is giving the output.
@ankitbansal62 жыл бұрын
@@LiveWithDebasmita Awesome
@gauravgupta55308 ай бұрын
I think method 1 and method 3 are the one that can be think of, 2nd approach requires more understanding of functions.
@tahakhalid3242 күн бұрын
hi ankit, here is my solution: WITH consec_seats AS ( SELECT seat_no, is_empty, LEAD(is_empty, 1, 'no data') OVER (ORDER BY seat_no) AS first_consec_ones FROM bms ), all_consec_seats AS ( SELECT seat_no, is_empty, first_consec_ones, LEAD(first_consec_ones, 1, 'no data') OVER (ORDER BY seat_no) AS second_consec_ones FROM consec_seats ), seat_numbers AS ( SELECT seat_no, CASE WHEN is_empty = 'Y' AND first_consec_ones = 'Y' AND second_consec_ones = 'Y' THEN LEAD(seat_no, 1, 0) OVER (ORDER BY seat_no) ELSE 0 END AS first_consec_ones, CASE WHEN is_empty = 'Y' AND first_consec_ones = 'Y' AND second_consec_ones = 'Y' THEN LEAD(seat_no, 2, 0) OVER (ORDER BY seat_no) ELSE 0 END AS second_consec_ones FROM all_consec_seats ) SELECT DISTINCT seat_no FROM ( SELECT seat_no FROM seat_numbers WHERE first_consec_ones 0 AND second_consec_ones 0 UNION ALL SELECT first_consec_ones FROM seat_numbers WHERE first_consec_ones 0 AND second_consec_ones 0 UNION ALL SELECT second_consec_ones FROM seat_numbers WHERE first_consec_ones 0 AND second_consec_ones 0 ) AS consecutive_seats ORDER BY seat_no;
@indergaming3053 Жыл бұрын
/* done brother */ /* 3 or more consective empty seats */ with cte as ( select *, lag(is_empty,1) over (order by seat_no) as prev1, lag(is_empty,2) over (order by seat_no) as prev2, lead(is_empty,1) over (order by seat_no) as nxt1, lead(is_empty,2) over (order by seat_no) as nxt2 from bms ) select seat_no from cte where is_empty='Y' and prev1='Y' and prev2='Y' or is_empty='Y' and prev1='Y' and nxt1='Y' or is_empty='Y' and nxt1='Y' and nxt2='Y'
@rashmidutta71512 жыл бұрын
last one was so cool!
@ujjwalvarshney3188 Жыл бұрын
select seat_no from ( select * , count(*) over(partition by rt ) as ct from ( select * , (seat_no - row_number() over (partition by is_empty order by seat_no)) as rt from bms where is_empty = 'Y') )where ct >= 3 order by 1
@livelovelaugh4050 Жыл бұрын
Thank you so much Sir🙏
@ankitbansal6 Жыл бұрын
Most welcome
@amitsaxena2114 Жыл бұрын
Thanks Ankit. Here is my approach to this problem with cte as ( SELECT * , LEAD(seat_no, 2) over (order by seat_no) as lead_seat_no from bms where is_empty = 'Y' ) , cte2 as ( (select seat_no, lead_seat_no from cte where lead_seat_no - seat_no = 2 ) ) select distinct b.seat_no from bms b join cte2 on b.seat_no between cte2.seat_no and cte2.lead_seat_no
@themightyvk Жыл бұрын
Just awesome...
@sudheerthulluri2 жыл бұрын
very good explanation
@kanchankumar33552 жыл бұрын
Very informative, will certainly help in next interview
@ankitbansal62 жыл бұрын
Thanks bro 😊
@bishwarup14292 жыл бұрын
My output is different but will this suffice? with cte as( select *, case when is_empty = "Y" and lead(is_empty,1) over() = "Y" and lead(is_empty, 2) over() = "Y" then 1 else 0 end as status from bms) select seat_no as first_seat,seat_no+1 as sec_seat, seat_no+2 as third_seat from cte where status = 1;
@NatureAndTravelAndAnimals2 жыл бұрын
Very interesting and nicely explained.
@ankitbansal62 жыл бұрын
Thank you 😊
@rahuldave66999 ай бұрын
SELECT seat_no from( SELECT *, sum(case when is_empty ="Y" then 1 else 0 END) over (order by seat_no rows between 2 preceding and current row) as prev_2, sum(case when is_empty ="Y" then 1 else 0 END) over (order by seat_no rows between 1 preceding and 1 following) as curr, sum(case when is_empty ="Y" then 1 else 0 END ) over (order by seat_no rows between current row and 2 following ) as next_2 from bms) tmp where prev_2 = 3 or curr =3 or next_2 =3
@apurvasaraf58287 ай бұрын
select seat_no from (select * ,count(d) over(partition by d) as v from (select seat_no,is_empty,seat_no-rank() over(partition by is_empty order by seat_no) as d from bms)p where is_empty='Y')j where v>=3
@helloranjan892 жыл бұрын
Good one
@ankitbansal62 жыл бұрын
Thank you 😊
@user-xy8lu9mo7v Жыл бұрын
Another Simpler way by just Using Lags & NO LEADS & Joins needed *********************************************************************** WITH cte as ( select * , CASE WHEN is_empty = 'Y' then 1 END as RNK , LAG(is_empty,3)over(order by CASE WHEN is_empty = 'Y' then 1 END) as LAGS from bms ) select seat_no, LAGS from cte where LAGS = 'Y' order by seat_no
@rudramurthy84062 жыл бұрын
Please increase the font size
@ankitbansal62 жыл бұрын
Done for recent videos
@monikamendiratta59002 жыл бұрын
Will method 2 work on mysql and postgres?
@ankitbansal62 жыл бұрын
Not sure. You can check functions .
@ParthKhambhayta3 ай бұрын
with cte as ( select a.seat_no a_seat_no, b.seat_no b_seat_no,c.seat_no c_seat_no, a.is_empty a_is_empty,b.is_empty b_is_empty,c.is_empty c_is_empty from bms a inner join bms b on a.seat_no + 1 = b.seat_no inner join bms c on a.seat_no + 2 = c.seat_no where a.is_empty = b.is_empty and a.is_empty = c.is_empty and a.is_empty = 'Y' and b.is_empty = 'Y' and c.is_empty = 'Y') select a_seat_no, a_is_empty from cte union select b_seat_no, b_is_empty from cte union select c_seat_no, c_is_empty from cte
@SuperMohit95 Жыл бұрын
2 approaches using MySQL -- approach 1 using lead function WITH cte2 AS( WITH cte AS ( SELECT *, LEAD(is_empty) OVER() AS lead_1 FROM bms ) SELECT *, LEAD(lead_1) OVER() AS lead_2 FROM cte ) SELECT seat_no, seat_no+1, seat_no+2 FROM cte2 WHERE is_empty = 'Y' AND lead_1 = 'Y' AND lead_2 = 'Y' ; -- using window functions WITH cte AS ( SELECT is_empty, seat_no, ROW_NUMBER() OVER(PARTITION BY is_empty) AS rn FROM bms ) SELECT seat_no FROM cte WHERE ABS(seat_no-rn) IN (3,2) ;
@sngv1Ай бұрын
WITH CT AS (SELECT *, LEAD(seat_no,1) OVER(ORDER BY seat_no) as next_seat, LEAD(seat_no,2) OVER(ORDER BY seat_no) as next2_seat FROM bms WHERE is_empty='Y'), CTE as ( SELECT * FROM CT WHERE next_seat-seat_no=1 AND next2_seat-seat_no=2) SELECT seat_no FROM CTE UNION SELECT next_seat FROM CTE UNION SELECT next2_seat FROM CTE
@saimahendrapatnaik2847 Жыл бұрын
Superb
@ShivamKumar-zl1qb7 ай бұрын
WITH cte1 AS ( SELECT seat_no, SUM(CASE WHEN is_empty = 'N' AND next_is_empty = 'Y' THEN 1 WHEN is_empty = 'Y' AND next_is_empty = 'N' THEN 1 ELSE 0 END) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sumover FROM ( SELECT seat_no, is_empty, LAG(is_empty, 1, is_empty) OVER () AS next_is_empty FROM bms ) z ) , cte2 AS ( SELECT sumover FROM cte1 GROUP BY sumover HAVING COUNT(seat_no) >= 3 ) SELECT seat_no FROM cte1 WHERE sumover IN (SELECT * FROM cte2) ;