Find repeating number runs of length N

number_runs.sql

IF OBJECT_ID('tempdb..#gaps','U') IS NOT NULL DROP TABLE #gaps; --SELECT * FROM #gaps
CREATE TABLE #gaps (
    ID int NOT NULL IDENTITY,
    Val int NOT NULL,
);

DECLARE @repeat int, @randval int, @sql nvarchar(MAX);
DECLARE @c int = 1000; -- How many islands to create
WHILE (@c > 0)
BEGIN;
    SELECT @repeat  = FLOOR(RAND(CHECKSUM(NEWID()))*(20))+1  -- How many consecutive rows to insert
        ,  @randval = FLOOR(RAND(CHECKSUM(NEWID()))*(100))+1 -- What random value to use
    
    SELECT @sql = CONCAT('INSERT INTO #gaps (Val) VALUES ','(',@randval,')', REPLICATE(CONCAT(',(',@randval,')'), @repeat-1))
    EXEC sys.sp_executesql @sql;

    SET @c -= 1;
END;

------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
-- Find islands of size N
DECLARE @n int = 10

SELECT x.IslandID, IslandStart = MIN(x.ID), IslandEnd = MAX(x.ID), Val = MAX(x.Val), IslandSize = COUNT(*)
FROM (
    SELECT x.ID, x.Val
        , IslandID = SUM(x.IslandStart) OVER (ORDER BY x.ID)
    FROM (
        SELECT g.ID, g.Val
            , IslandStart = IIF(g.Val <> LAG(g.Val, 1, 0) OVER (ORDER BY g.ID), 1, 0)
        FROM #gaps g
    ) x
) x
GROUP BY x.IslandID
HAVING COUNT(*) = @n
ORDER BY x.IslandID

[reserving first comment]