Database Challenges

--Razvan_Socol_TSQLChallange_20.sql
WITH Fibo AS (
	SELECT CONVERT(bigint,0) AS A, CONVERT(bigint,1) AS B, CONVERT(bigint,1) AS C
	UNION ALL
	SELECT B AS A, C AS B, B+C AS C
	FROM fibo
	WHERE B<4611686018427387904 -- 2^62
),
Digits AS (SELECT CONVERT(char(1),N) D FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) V(N)),
Repeats AS (
	SELECT C AS FiboNumber,
	--SUM(LEN(CONVERT(varchar(19),C))-LEN(REPLACE(CONVERT(varchar(19),C),D+D,'')))/2 AS NumRepeats
	SUM(CASE WHEN LEN(CONVERT(varchar(19),C))-LEN(REPLACE(CONVERT(varchar(19),C),D+D,''))>0 THEN 1 ELSE 0 END) AS NumRepeats
	FROM fibo CROSS JOIN Digits GROUP BY C
), 
Ranks AS (
	SELECT NumRepeats, FiboNumber, 
	RANK() OVER (PARTITION BY NumRepeats ORDER BY FiboNumber) Rnk
	FROM Repeats WHERE NumRepeats>0
)
SELECT NumRepeats, FiboNumber
FROM Ranks WHERE Rnk<=5

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.