LeetCode 603 - Consecutive Available Seats | SQL Solution [EASY]

Today I solve and explain a LeetCode SQL problem using MySQL called "603. Consecutive Available Seats"

Code Written Solution Blog (you need leetcode account):

If you found this helpful Like and Subscribe! I solve LeetCode Algorithms and Database/ SQL Problems daily!

#leetcode #mysql #sql #tutorial #SQLinterviewQuestions #LeetCodeSQL #FAANG #coding #programming

[skysum]

Hey Carter, thanks for attempting this question on a live demo.
I'd really appreciate if you can share the source of this code. I wanted to understand how the 'where' clause is working to arrive at the solution. Thanks!

[siddheshkalgaonkar]

Nice solution. However, I didn't get that where clause part. Could you please explain that?

[siddheshkalgaonkar]

This worked for me. The overall idea is to make a group of consecutive numbers and then find the largest group of consecutive numbers and return the seat_ids associated with that group.

with data as (
select
seat_id,
free,
case
when lead(free) over(
order by
seat_id
) != free then 0
else 1
end as diff
from
driver
),
max_diff as (
select
seat_id
from
data
where
diff = (
select
diff
from
data
group by
diff
order by
count(*) desc
limit
1
)
)
select
seat_id
from
max_diff

[pushankarmakar]

is this ok?

SELECT C1.seat_id
FROM Cinema AS C1
INNER JOIN Cinema AS C2
ON C2.seat_id = C1.seat_id +1
WHERE C1.free = 1 AND C2.free=1
ORDER BY C1.seat_id;