Database Challenges

--antonio_facturan_tsqlchallenge_20.sql
;WITH Fibonacci(fValue, ctr, flag)
AS
(
	SELECT CAST(0 AS BIGINT), CAST(1 AS BIGINT), 0
	UNION ALL
	SELECT ctr, fValue + ctr, flag + 1
	FROM Fibonacci
	WHERE flag + 1 < 92
),
Occurrence(FiboNumber, OccurCtr, NumRepeats)
AS
(
	SELECT fValue, 0, CASE WHEN CHARINDEX(REPLICATE(0, 2), fValue) > 0 THEN 1 ELSE 0 END
	FROM Fibonacci
	WHERE fValue > 34
	UNION ALL
	SELECT FiboNumber, OccurCtr + 1, CASE WHEN CHARINDEX(REPLICATE(OccurCtr + 1, 2), FiboNumber) > 0 THEN 1 ELSE 0 END
	FROM Fibonacci
	INNER JOIN Occurrence ON fValue = FiboNumber
	WHERE OccurCtr + 1 < 10
	
),
FinalSource(NumRepeats, FiboNumber, RowNumber)
AS
(
	SELECT NumRepeats, FiboNumber, ROW_NUMBER() OVER(PARTITION BY NumRepeats ORDER BY FiboNumber)
	FROM
	(
		SELECT SUM(NumRepeats) AS NumRepeats, FiboNumber
		FROM Occurrence
		GROUP BY FiboNumber
		HAVING SUM(NumRepeats) > 0
	) AS src
)
SELECT NumRepeats, FiboNumber
FROM FinalSource
WHERE RowNumber <= 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.