Database Challenges

--maciej_pilecki_tsqlchallenge_20.sql
		
WITH Fibonacci AS ( 
	SELECT 1 AS Step, CAST(1 AS bigint) AS Value, CAST(0 AS bigint) AS Prev
	UNION ALL
	SELECT Step+1, Value+Prev, Value
	FROM Fibonacci 
	WHERE Step < 92),
Digits AS (
	SELECT 0 AS d
	UNION ALL
	SELECT 1
	UNION ALL
	SELECT 2
	UNION ALL
	SELECT 3
	UNION ALL
	SELECT 4
	UNION ALL
	SELECT 5
	UNION ALL
	SELECT 6
	UNION ALL
	SELECT 7
	UNION ALL
	SELECT 8
	UNION ALL
	SELECT 9),
Repeats AS (
	SELECT COUNT(*) AS NumRepeats, f.Value AS FiboNumber 
	FROM Fibonacci f
		JOIN Digits d
			ON CHARINDEX(REPLICATE(d, 2), f.Value, 1)>0
	GROUP BY f.Value),
Ranking AS (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY NumRepeats ORDER BY FiboNumber) AS rn
	FROM Repeats )
SELECT NumRepeats, FiboNumber 
FROM Ranking
WHERE rn <= 5
ORDER BY 1, 2

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.