LeetCode Medium 1285 Interview SQL Question with Detailed Explanation | Practice SQL

In this video I solve and explain a medium difficulty 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 includes points to keep in mind to develop SQL queries.

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

[dgvj]

Your Leetcode SQL videos are hands down the best. I am really waiting for the similar LeetCode Series on Python Data Structure questions.

[mardinliserseri]

U r the obviously the best solver of leetcode sql. Thanks for your efforts.

[prnvsgr]

wow logic ✏( 30 min se laga hua tha per ye nhi socha tha)

[haleinan]

Hey my friend, this is amazing, would be great if you could do similar series for python coding challenges.

[adityams]

*This was my approach with lead and lag, *
*Its pretty inefficient since I am using 3 ctes.*
*My mind blew once I saw your solution to it!*


*more details on the logic:*
doing *lead - log_id* or *log_id - lag* will result in a column which has *nulls, 1, other_number*
*if there is a sequence we will find null or 1 with corresponding row*
*if there is a break in a sequence we will get any number other than 1*
so we select rows only which has condition as Not 1 and IS NULL.


WITH cte AS (
SELECT
log_id,
lag(log_id) OVER (ORDER BY log_id) AS lag,
lead(log_id) OVER (ORDER BY log_id) AS lead
FROM
Logs
),
cte_low AS (
SELECT
log_id,
row_number() OVER () AS id
FROM
cte
WHERE
lead - log_id <> 1
OR lead - log_id IS NULL
),
cte_high AS (
SELECT
log_id,
row_number() OVER () AS id
FROM
cte
WHERE
log_id - lag <> 1
OR log_id - lag IS NULL
)
SELECT
cte_low.log_id AS start_id,
cte_high.log_id AS end_id
FROM
cte_low
LEFT JOIN cte_high ON cte_low.id = cte_high.id;

[florincopaci]

Hello ! also a way to see if numbers are consecutive is if (a+c)/2=b then the numbers are consecutive.

[devanshsharma]

Hey thank you so much for the inspiration..
my code:
select log_id as start_id, log_id + count(*) - 1 as end_id from
(select *, log_id - rank() over(order by log_id) as rnk
from logs) as tmp
group by rnk