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)

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

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.

next level of concepts clearing..thank you so much sir.. maja aa gaya ye video dekh kar..

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.

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

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?

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

Thanks for video ankit... 🙏 One request if you get time please make videos on python also.

Too Good Ankit. Enlightening and Easy to Grasp.

Method 3 is so elegant.

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;

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;

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

i wish i had watcvhed your videos before attending latham and watkins he asked about window functions and cte too

You are really awesome!!, your way of explaining makes every logic clear!! Keep making such videos so we can learn more from this Videos

DAMN, i would have never thought of the second method

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

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

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

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;

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

thank you so much sir for valuable content ! 🙌

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;

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

as usual, the greatest.

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 ;

I think method 1 and method 3 are the one that can be think of, 2nd approach requires more understanding of functions.

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;

/* 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'

last one was so cool!

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

Thank you so much Sir🙏

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

Just awesome...

very good explanation

Very informative, will certainly help in next interview

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;

Very interesting and nicely explained.

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

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

Good one

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

Please increase the font size

Will method 2 work on mysql and postgres?

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

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

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

Superb

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