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
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...
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).
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.
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
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;
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.
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.
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
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)
@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 😊
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
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 🙂
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
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
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;
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 ;
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
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
![SahBabii - Viking [Official Music Video]](http://i.ytimg.com/vi/D37cL92JX0I/mqdefault.jpg)
really amazing... thanks for sharing.
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
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...
Glad to know that the explanation was helpful. Thanks for appreciating, Rofi.
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).
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.
this guy is a blessing
Great explanation, really helpful, Please keep making more sql related videos,
Definitely, videos are coming daily 😊
I really loved this playlist.
Thankyou.
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
Thanks for such kind words. Glad that you are finding the videos useful 😊
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;
Amazing series with lucid explanation, thnkyou
Glad that you are finding the videos helpful.
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
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.
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.
Thanks for this clear explaination
Glad that you found this video useful, Rawal.
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
Bro please make video on over clause........
Can we add null condition instead of introducing lag?
i think if you use Null , then Seatid 1 will also come in result...
@@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
can you give any suggestion to practice
can we solve this w/o the lead lag, using joins?
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)
@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 😊
Another approach
select seat_id from (
select *,(seat_id-lag(seat_id,1) over () ) as diff
from cinema )A where free-diff=0;
thankyou
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?
We can't check null value like this
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
Please provide us the solution using the github link.
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
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 🙂
Hey Abdul, sure I’ll start on the playlist very soon. I’m glad that you are finding these videos useful 😊
@@EverydayDataScience Thanks, keep going 😊
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
You can use common table expression to optimise even further
4TH ONE
Awesome!
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
i did the same~
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;
this looks like a good solution
I did not understand how it worked out. Can u please explain me
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 ;
Sir, if you have LinkedIn account, give me please
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
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