Database Challenges

--Arumugam_Thiraviam_TSQLChallenge_20_2_v2.sql
;WITH fiba1 AS
(
	-- RECURSIVE CTE TO GENERATE FIBANOCCI SERIES
	SELECT 1 AS rownum, 
		   CAST(0 AS BIGINT) AS prevnum, 
		   CAST(1 AS BIGINT) AS num
	 UNION ALL
	SELECT rownum + 1, 
		   num, 
		   num + prevnum
	  FROM fiba1
	 WHERE rownum + 1 <= 92
)
, fiba AS
(
	-- CONVERT THE NUMBER TO VARCHAR. THIS IS NEEDED TO CHECK FOR REPEATING ADJACENT NUMBERS
	SELECT CAST(num AS VARCHAR(20)) AS data 
	  FROM fiba1 
	 WHERE num >= 10
)
, numbers AS
(
	-- NUMBER TABLE
	SELECT '00' number UNION ALL
	SELECT '11' UNION ALL
	SELECT '22' UNION ALL
	SELECT '33' UNION ALL
	SELECT '44' UNION ALL
	SELECT '55' UNION ALL
	SELECT '66' UNION ALL
	SELECT '77' UNION ALL
	SELECT '88' UNION ALL
	SELECT '99'
)
SELECT cnt AS NumRepeats,
       data AS FiboNumber
  FROM
(
	SELECT data, 
		   COUNT(*) cnt,
		   ROW_NUMBER() OVER( PARTITION BY COUNT(*) ORDER BY CAST(data AS BIGINT)) AS Number
	  FROM fiba f 
	 CROSS JOIN numbers n
	 WHERE LEN(data) - LEN(REPLACE(data, number, '')) > 1
	 GROUP BY data

) a WHERE number <= 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.