Find the numbers that appear at least three times consecutively

This is one of the most asked SQL interview question find the the numbers that appear at least three times consecutively.

As,  you can see the input table named Numbers.

CREATE TABLE
Numbers(Id INT PRIMARY KEY IDENTITY(1,1), num INT)

Let’s insert some sample records into this table.

INSERT INTO Numbers
VALUES (3), (3),(3), (3),(2),(1),(1),(5),(1),(5),(5),(5),(2)

You can see the records in table.

SELECT * FROM Numbers;

Solution:

Below T-SQL find the the numbers that appear at least three times consecutively.

WITH Records AS (
SLECT id, num,
id - ROW_NUMBER()OVER(PARTITION BY num ORDER BY num ) + 1 AS rnk
from Numbers
)
SELECT DISTINCT num AS ConsecutiveNums
FROM Records
GROUP BY num, rnk
HAVING count(rnk) >= 3

You can see the output of above T-SQL.