Skip to main content

LeetCode 180. Consecutive Numbers SQL Solution

Problem​

LeetCode SQL Problem

  1. Consecutive Numbers

Logs table

IdNum
11
21
31
42
51
62
72
82
95
105
115

Solution - Self Join and DISTINCT​

To find all numbers that appear at least three times consecutively, we perform self join on Logs table so we have previous number, next number, and current number all in the same row.

When the value of the current number equals to the previous number and the next number, we know this number appears at least 3 times consecutively.

We use DISTINCT keyword to not return duplicate numbers in the output.

MySQL Solution
SELECT DISTINCT A.Num AS ConsecutiveNums
FROM Logs AS A
INNER JOIN Logs AS B ON A.Id + 1 = B.Id
INNER JOIN Logs AS C ON B.Id + 1 = C.Id
WHERE A.Num = B.Num
	AND B.Num = C.Num

Query Output

ConsecutiveNums
1
2
5

Solution - Self Join and Group By​

This is the same as the self join solution discussed above, we use Group By to ensure there is no duplicate number in the output.

SELECT A.Num AS ConsecutiveNums
FROM Logs AS A
INNER JOIN Logs AS B ON A.Id + 1 = B.Id
INNER JOIN Logs AS C ON B.Id + 1 = C.Id
WHERE A.Num = B.Num
	AND B.Num = C.Num
GROUP BY A.Num

Query Output

ConsecutiveNums
1
2
5

Solution - Lag() and Lead() Window Functions​

To find all numbers that appear at least three times consecutively, we utilize Lag() and Lead() Windows Functions to put previous number, next number, and current number all in the same row.

When the value of the current number equals to the previous number and the next number, we know this number appears at least 3 times consecutively.

We then use DISTINCT keyword to not return duplicate numbers in the output.

WITH ConsecutiveNumAnalysis
AS (
	SELECT Id
		,Num AS CurrNum
		,lag(Num) OVER (
			ORDER BY Id
			) AS PrevNum
		,lead(Num) OVER (
			ORDER BY Id
			) AS NextNum
	FROM Logs
	)
SELECT DISTINCT CurrNum AS ConsecutiveNums
FROM ConsecutiveNumAnalysis
WHERE CurrNum = PrevNum
	AND CurrNum = NextNum

Query Output

ConsecutiveNums
1
2
5