LeetCode Medium 180 'Consecutive Numbers' Amazon Uber Interview SQL Question Detailed Explanation

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

[EverydayDataScience]

Working solution:

WITH cte AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY num ORDER BY id)
AS rnk
FROM Logs),

cte2 AS
(SELECT *, (id*1.0-rnk*1.0) AS diff. // multiplication with 1.0 is done so that SIGNED/UNSIGNED difference error doesn't occur
FROM cte)

SELECT DISTINCT num AS ConsecutiveNums
FROM cte2
GROUP BY diff, num
HAVING COUNT(*) >= 3;

[aspirationqueen]

when id's are jumbled up and id's don't follow sequence use this query with cte as
(
select id, num, lead(num,1) over(order by id) as next, lead(num, 2) over(order by id ) as next_2_next,
lead(id, 1) over(order by id) as next_team_id, lead(id, 2) over(order by id) as next_2_next_team_id
from Logs
)

select distinct num as ConsecutiveNums
from cte
where (num=next and num=next_2_next)
and (id+1=next_team_id and id+2=next_2_next_team_id)

[saisaketh]

These videos are very helpful in learning sql!!!!

[juneyou]

Hi Bro, The given solution is not working currently. Not sure if test cases are updated. Kindly help us if there is any other easy approach to solve this. Thank you

[PiyushKumar-tvdr]

for sql server? we can't mention inside the argument in lead function

[KshitijSoni-kngp]

This won't work when Id's are jumbled up

[朱怡蓁-uy]

why can't we write where num = next_1 = next_2? Thank you!

[akhand-xy]

WITH cte as (
SELECT id, num, LEAD(num) OVER(ORDER BY id) as nextnum, LEAD(num, 2) OVER(ORDER BY id) as nextnum2
FROM Logs
)

SELECT DISTINCT num as ConsecutiveNums
FROM cte
WHERE num=nextnum and nextnum=nextnum2 and num=nextnum2;

[atharvaathalye]

test case with 4 1's are not accepting.

[ce__sunny_nath]

Sir please update the solution since question is telling that atleast three so there can be many more occurrences for same no.

[massvinod]

This solution is not working for some test cases. Please check it once .

[rayyanamir]

Thanks sir for such a great explanation. What's your paypal?