Database Challenges

--alun_davies_tsqlchallenge_20.sql
with Fibonacci ( ref, lastbig , thisbig, cc)
as
(select cast(1 as int) ,cast(0 as bigint), cast(1 as bigint) ,  CAST(0 as int)
union all
select (ref+1), thisbig , cast(lastbig + thisbig as bigint) ,
case when charindex('00',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('11',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('22',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('33',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('44',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('55',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('66',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('77',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('88',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end
+case when charindex('99',cast(lastbig + thisbig as varchar(32))) =0 then 0 else 1 end from Fibonacci where ref < 92 )
select cc, thisbig from 
(select ref
,cc
,thisbig
,RANK() OVER (PARTITION BY cc ORDER BY ref ) AS 'RANK'
from Fibonacci where cc <> 0 ) as fib
where RANK < 6
 

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.