LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 603 | Window Fun

[@justcodeitbro1312]

this guy is a blessing

[@energizer9814]

One of the another approch we can try is SELECT sub.seat_id FROM (SELECT *, LEAD(free) OVER(ORDER BY seat_id) AS NextSeat FROM Cinema) AS sub WHERE sub.free = 1 AND (sub.NextSeat = 1 OR sub.NextSeat IS Null) ORDER BY sub.seat_id; I hope this might run faster, not very sure

[@Lucky-hh4cg]

really amazing... thanks for sharing.

[@samuraibhai007]

Thank you. I tried using LAG / LEAD for this question but was unable to solve it. Your explanation clarified things a bit more. I don't think this is an "Easy" problem as LeetCode has labeled it...

[@EverydayDataScience]

Glad to know that the explanation was helpful. Thanks for appreciating, Rofi.

[@alokkumardutta3298]

Hello, I recently discovered your channel through the comment section of an Instagram post, and after watching a few videos, I'm glad I found your channel. Thank You so much for sharing your knowledge with us. I'd really love to hear some career advice from you when it comes to data science (maybe a new video series where you can discuss tips for beginners).

[@EverydayDataScience]

Glad that you are finding the videos useful, Alok. Thanks for such kind words. Sure, I am thinking about it as well. Will soon try to come up with some videos.

[@imdeepu7855]

select w.seat_id from (select *, ifnull(( LEAD(free) over (order by seat_id)),"1") as Next_Seat from Cinema) as w where w.free =1 and w.Next_Seat=1

[@anonymous-ze5fg]

Great explanation, really helpful, Please keep making more sql related videos,

[@EverydayDataScience]

Definitely, videos are coming daily 😊

[@asifbasheerkhan2855]

I really loved this playlist. Thankyou.

[@abhisheksharma6617]

It's a little easier to follow, using a CTE : with cte as (select *, lead(free) over(order by seat_id) ld, lag(free) over(order by seat_id) lg from cinema) select seat_id from cte where free=ld or free=lg order by seat_id;

[@muhammadasjadsheikh7502]

hey Buddy I just Recently came across with your channel and found it very useful your explanation and approach is very good Thanks for such a Informative Content

[@EverydayDataScience]

Thanks for such kind words. Glad that you are finding the videos useful 😊

[@T5AGamerz]

Amazing series with lucid explanation, thnkyou

[@EverydayDataScience]

Glad that you are finding the videos helpful.

[@Ilovefriendswebseries]

Another approach select seat_id from ( select *,(seat_id-lag(seat_id,1) over () ) as diff from cinema )A where free-diff=0;

[@manasagiduthuri6557]

Hello @Everyday Data Science, your video lectures are awesome . Thanks for making such video lectures.. but how can i practice all leetcode questions without subscription

[@348_rahulbehera4]

Hey @Everyday Data Science, your video lectures are awesome 🙌. Thanks for making such video lectures 👍🤗. PS: Add the Problem link in the description so that we can access the questions directly.

[@EverydayDataScience]

Glad that you are finding it useful. Yes I have started adding it in the description now. Some initial videos don’t have it but now every videos after those initial ones have the links as well.

[@rawalrohit7929]

Thanks for this clear explaination

[@EverydayDataScience]

Glad that you found this video useful, Rawal.

[@manasagiduthuri6557]

can you give any suggestion to practice

[@svanaja331]

Bro please make video on over clause........

[@atharvameher5880]

can we solve this w/o the lead lag, using joins?

[@sany2k8]

Can we add null condition instead of introducing lag?

[@Manojkumar__]

i think if you use Null , then Seatid 1 will also come in result...

[@itechinnovations2200]

​@@Manojkumar__ how come the first will come in results becz for the first one next seet is not equal to zero as well as is not not null since it zero

[@dhawalsood2654]

great explanation. just 1 question where u have written w.free=1 and w.nextseat=1. Can't we write for 0 also?? (w.free=1 and w.nextseat=1) OR (w.free=0 and w.nextseat=0)

[@tejaswaniguttula5961]

@dhawalsood2654 Here free column having values 0 or 1 (Boolean ) 1 means that the seat is free not yet filled 0 means seat is filled not free. So, (1,1) means both seats are free . (0,0) Means both seats are not free(filled) (1,0) Or (0,1) one of the seats are filled. Hope you understood. Happy learning 😊

[@PawanSingh-jx8nu]

create table #Cinema ( seat_id int primary key identity(1,1), free bit ) insert into #Cinema values (1),(0),(1),(1),(1) select * from #Cinema select seat_id from ( select *, lead(free)over(order by seat_id) nextSeat, lag(free) over(order by seat_id)prevset from #Cinema ) s where nextSeat=1 and free=1 or prevset=1 and free=1

[@soumikdey1456]

instead i=of adding LAG function, can we write like WHERE W.free = 1 and W.NextSeat = 1 or W.free = 1 and W.NextSeat = null?

[@amitshankhwar2542]

We can't check null value like this

[@pavanch9256]

Please provide us the solution using the github link.

[@nazrusheriff7229]

SELECT seat_id FROM cinema WHERE free = 1 AND ( seat_id + 1 IN (SELECT seat_id FROM cinema WHERE free = 1) OR seat_id - 1 IN (SELECT seat_id FROM cinema WHERE free = 1) ); This can also be a solution

[@vamsimadugula8524]

You can use common table expression to optimise even further

[@dancingindia24]

thankyou

[@sagarghare9829]

I like your video. I guess you should teach on normal white paper or iPad with pencils with examples. It will be easier to catch up and remember

[@maheshodedra8609]

Hi There, Thanks for the Great explanation we can use this approach as well. Select c1.seat_id, c2.seat.id, c3.seat_id from Cinema c1 join Cinema c2 on c1.seat_id + 1 = c2.seat.id and c1.free = c2.free join Cinema c3 on c1.seat_id + 2 = c3.seat.id and c1.free = c3.free

[@luyaowang4606]

i did the same~

[@AbdulRahman-zp5bp]

Bro, Please start a playlist of python where you solve interview Questions(of level Easy, Medium to Hard) while explaining logic, I'll be waiting Thanks 🙂

[@EverydayDataScience]

Hey Abdul, sure I’ll start on the playlist very soon. I’m glad that you are finding these videos useful 😊

[@AbdulRahman-zp5bp]

@@EverydayDataScience Thanks, keep going 😊

[@mlvprasadofficial]

4TH ONE

[@EverydayDataScience]

Awesome!

[@pankajchandel1000]

this will only work if the seat_id increase by 1. select distinct t1.seat_id from Cinema as t1 join Cinema as t2 on abs(t1.seat_id -t2.seat_id) =1 and t1.free =1 and t2.free=1 order by t1.seat_id;

[@ansylpinto2301]

this looks like a good solution

[@pragatiaggarwal8103]

I did not understand how it worked out. Can u please explain me

[@pankajchandel1000]

select C.seat_id from (select *,LEAD(free) over(ORDER BY seat_id ) as Next,LAG(free) over(ORDER BY seat_id) as Prev from Cinema) as C where C.free =1 and C.Next=1 or C.free=1 and C.prev=1) ORDER BY C.seat_id ;

[@yassinbenyahia6801]

I don't think it is an easy problem here is a more simple solution: "select distinct S1.seat_id from Cinema S1 join Cinema S2 on (Abs(S1.seat_id-S2.seat_id)=1) and S1.free=1 and S2.free=1 order by S1.seat_id ASC ;"

[@Happyface-dm5nh]

MY CODE SELECT SEAT_ID FROM (select SEAT_ID AS SEAT_ID , FREE , LEAD (free) OVER (ORDER BY SEAT_ID) AS NEXT_SEAT,LAG(free) OVER (ORDER BY SEAT_ID) AS PRE_SEAT from Cinema ) WHERE FREE = NEXT_SEAT OR FREE = PRE_SEAT

[@surbhirautaray6872]

Sir, if you have LinkedIn account, give me please

[@energizer9814]

One of the another approch we can try is SELECT sub.seat_id FROM (SELECT *, LEAD(free) OVER(ORDER BY seat_id) AS NextSeat FROM Cinema) AS sub WHERE sub.free = 1 AND (sub.NextSeat = 1 OR sub.NextSeat IS Null) ORDER BY sub.seat_id; I hope this might run faster, not very sure