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

[@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)

[@supriyakushwaha21]

I WAS WONDERING WHY THE CODE IN THIS VIDEO IS NOT WORKING. BUTY, AFTER WRITING YOUR CODE , ALL TESTCASES PASSED. THANKS

[@saisaketh8433]

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

[@Thevisionaryaddy]

loved the solution , thanks

[@june17you]

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

[@kelvinlow823]

brilliant solution thanks

[@oliver_comp]

explanation top 100%

[@EverydayDataScience]

Glad that you found the video useful 😊

[@gowtham4383]

Great. Thanks

[@PiyushKumar-tv6dr]

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

[@RajeshKumar._.16]

nice

[@digitaldruglord7232]

perfectly explained, Thnx

[@KshitijSoni-kn2gp]

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

[@aspirationqueen]

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) can use this one

[@EverydayDataScience]

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;

[@rupdeepthey]

Awesome !!

[@EverydayDataScience]

Glad that you found the video useful 😊

[@callmeruks]

Great Explanation

[@EverydayDataScience]

Glad that you found it useful, Rukshar.

[@callmeruks]

@@EverydayDataScience Can you make a video on Tree node -problem 608

[@朱怡蓁-u9y]

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

[@rupdeepthey]

Fails for negative nums idk why.

[@atharvaathalye]

test case with 4 1's are not accepting.

[@EverydayDataScience]

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;

[@ce_070_sunny_nath8]

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

[@aakashmittal8598]

use DISTINCT in SELECT statement:
WITH cte AS(
SELECT *, LEAD(num,1) OVER() AS NEXT_1,
LEAD(num,2) OVER() AS NEXT_2
FROM Logs)
SELECT DISTINCT num AS ConsecutiveNums
FROM cte
WHERE num = NEXT_1 AND
num = NEXT_2;

[@EverydayDataScience]

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;

[@massvinod8413]

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

[@EverydayDataScience]

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;

[@rayyanamir8560]

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

[@AdarshChikkaswamygowdaAdarsh]

[@amanraheja2905]

brilliant solution thanks