Database Challenges

--Kevin_Kershaw_tsqlchallenge_20.sql
with fibgen as (
select cast(0 as bigint) as n1, cast(1 as bigint) as n2
union all
select n2, n1 + n2 from fibgen
)
, pass1 as (
select top 92 n2 as n, cast(n2 as varchar(20)) ns
from fibgen
)
, pass2 as (
select n, ns
	, case when ns like '%11%' then 1 else 0 end as dig1
	, case when ns like '%22%' then 1 else 0 end as dig2
	, case when ns like '%33%' then 1 else 0 end as dig3
	, case when ns like '%44%' then 1 else 0 end as dig4
	, case when ns like '%55%' then 1 else 0 end as dig5
	, case when ns like '%66%' then 1 else 0 end as dig6
	, case when ns like '%77%' then 1 else 0 end as dig7
	, case when ns like '%88%' then 1 else 0 end as dig8
	, case when ns like '%99%' then 1 else 0 end as dig9
	, case when ns like '%00%' then 1 else 0 end as dig0
from pass1
)
, pass3 as (
select n, ns, dig1 + dig2 + dig3 + dig4 + dig5 + dig6 + dig7 + dig8 + dig9 + dig0 as dc
from pass2
)
, pass4 as (
select n, ns, dc, row_number()over(partition by dc order by n) as rownum
from pass3
where dc > 0
)
select dc as NumRepeats, ns as FiboNumber
from pass4
where rownum <= 5
order by dc, n

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.