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

Previous Video: LeetCode 183 Customers Who Never Order

In this video I solve and explain a leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question is about finding consecutive available seats in a Cinema hall and also includes points to keep in mind to develop SQL queries. You will also learn about WIndow functions like LEAD, LAG and subqueries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[energizer]

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

[samuraibhai]

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

[alokkumardutta]

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

[anonymous-zefg]

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

[asifbasheerkhan]

I really loved this playlist.
Thankyou.

[muhammadasjadsheikh]

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

[abhisheksharma]

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;

[TAGamerz]

Amazing series with lucid explanation, thnkyou

[imdeepu]

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

[_rahulbehera]

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.

[manasagiduthuri]

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

[dhawalsood]

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)

[Ilovefriendswebseries]

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

[sanyk]

Can we add null condition instead of introducing lag?

[atharvameher]

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

[manasagiduthuri]

can you give any suggestion to practice

[soumikdey]

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?

[maheshodedra]

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

[pavanch]

Please provide us the solution using the github link.

[PawanSingh-jxnu]

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