Database Challenges

--Divya_Agrawal_tsqlchallenge_20.sql
;with cte1 as
(
	--Generate Fibonacci series
	select cast(1 as bigint) as fib1,cast(1 as bigint) as fib2,1 as cnt
	union all
	select fib2,fib1+fib2,cnt+1
	from cte1 
	where cnt < 91
),
 cte2 as
(
	-- split fib nos on digit sequence
	select	fib2 AS fib,
			cnt,			
			len(cast(fib2 as varchar))  as dgtno			
	from cte1 c1
	union all
	select	c2.fib,
			c2.cnt,			
			c2.dgtno-1 as dgtno			
	from cte2 c2 
	inner join cte1 c1 on c2.cnt=c1.cnt
	where c2.dgtno > 1
),
cte3 as
(
	-- split fib nos digits on digit sequence
	select	*,
			cast(substring(cast(fib as varchar),dgtno,1) as varchar) as dgt
	from cte2
),
cte4 as
(
	-- group consecutive occurence of fib nos digits
	select	*,
			dgtno - ROW_NUMBER() OVER (PARTITION BY cnt,dgt ORDER BY dgtno ) as grp
	from cte3
),
cte5 as
(
	-- take consecutive occcurences having more than one repeats
	select	fib,
			cnt,
			dgt				
	from cte4
	group by fib,cnt,dgt,grp
	having count(*)>1
)
select	rpts AS NumRepeats,
		fib as FiboNumber 
from
(
	-- order minimum 5 fib nos on repeating digits
	select	fib,
			cnt,
			count(distinct dgt) as rpts, 
			ROW_NUMBER() OVER(PARTITION BY COUNT(distinct dgt) ORDER BY fib) as minfibrpts
	from cte5
	group by fib,cnt
)T
where minfibrpts < 6
order by rpts,fib

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.