Database Challenges

--jeff_hadden_tsqlchallenge_20.sql
WITH FIBONACCI (n,f,f1,NumRepeats) AS
(       SELECT  CAST(1 AS BIGINT),
                CAST(0 AS BIGINT),
                CAST(1 AS BIGINT),
                NULL
        UNION ALL
        SELECT  n + 1,
                f + f1,
                f,
       			CASE WHEN CHARINDEX('00',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('11',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('22',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('33',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('44',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('55',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('66',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('77',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('88',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END +
				CASE WHEN CHARINDEX('99',CONVERT(VARCHAR,f+f1)) <> 0 THEN 1 ELSE 0 END AS NumRepeats
		FROM    Fibonacci
        WHERE   n BETWEEN 1 AND 92
)

SELECT	NumRepeats,
		FiboNumber
FROM
	(SELECT  NumRepeats,
			f AS FiboNumber,
			RANK() OVER(PARTITION BY NumRepeats ORDER BY f) AS RankNumber
	FROM    Fibonacci
	WHERE	NumRepeats > 0) temp
WHERE		RankNumber BETWEEN 1 AND 5
ORDER BY NumRepeats, FiboNumber

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.