Database Challenges

--Jon_Cooney_tsqlchallenge_20.sql
--Use a Common Table Expression to build the table of Fibonacci numbers.
--Column x is sacrificial, to count the requested number of Fibonacci numbers - 92.

WITH CTE(x,y,z)
AS
(
	SELECT  x = 0,y = CONVERT (BIGINT,1), z = CONVERT (BIGINT,0)
	UNION ALL
	SELECT x = x+1, y = z, z = z+y
		FROM CTE
		WHERE x < 92
)

--Use Charindex to identify position of pattern in z. Sign converts position to 0 or 1.
--Using Charindex ensures CTE is called only once.
--Row_number sorts the table and numbers the rows for the filter.
--Partition By restarts the numbering for each number of repeats.
--Filter using the RowNumber column.

SELECT t.NumRepeats, t.FiboNum
	FROM
	(
		SELECT s.NumRepeats, s.z AS FiboNum, ROW_NUMBER() OVER (PARTITION BY NumRepeats ORDER BY z) AS 'RowNumber'
			FROM
			(
				SELECT SIGN(CHARINDEX('00',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('11',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('22',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('33',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('44',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('55',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('66',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('77',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('88',CAST(z AS VARCHAR(20))))
						+SIGN(CHARINDEX('99',CAST(z AS VARCHAR(20)))) AS NumRepeats
					,z
					FROM CTE
			) AS s
		WHERE s.NumRepeats > 0
	) AS t
	WHERE t.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.