Database Challenges

--Mario_Puskaric_tsqlchallenge_20_v2.sql
/*
TSQL Challenge 20 - Identify repeating digits in Fibonacci Series
http://beyondrelational.com/blogs/tc/archive/2009/12/28/tsql-challenge-2...

This challenge has absolutely no relevance to a real-life problem,
but it is very interesting because it tests your programming skills and abstract thinking.

The Fibonacci series is defined as 1, 1, 2, 3, 5, 8, 13, 21, 34, 55 etc, where each number
in the series is the sum of the two numbers before it. For example: 34 = 13+21, 55 = 21+34 etc.
Some Fibonacci numbers have repeating digits, like 55 for example, which has a single 
repeating digit. The Fibonacci number 17711 has 2 (different) repeating digits.

Your job is to look through the first 92 Fibonacci numbers (since the 93rd number is beyond
BIGINT range) and produce a result set showing the 5 lowest Fibonacci numbers for each
quantity of (different) repeating digits. 


*/
-- Mario Puskaric 2010-01-04
;with Fibonacci(n, f, f1, l) as
(   select  cast(1 as bigint),
            cast(0 as bigint),
            cast(1 as bigint),
            cast(0 as int)
    union all
    select  n + 1,
            f + f1,
            f,
            sign(charindex('00', f + f1)) +
            sign(charindex('11', f + f1)) +
            sign(charindex('22', f + f1)) +
            sign(charindex('33', f + f1)) +
            sign(charindex('44', f + f1)) +
            sign(charindex('55', f + f1)) +
            sign(charindex('66', f + f1)) +
            sign(charindex('77', f + f1)) +
            sign(charindex('88', f + f1)) +
            sign(charindex('99', f + f1))
    from    Fibonacci
    where   n < 93
)
select  NumRepeats = l,
        FiboNumber = f
from
    (select  l,
             f,
             rbr = row_number() over (partition by l order by n)
     from    Fibonacci
     where   l > 0
    )fib
where	 rbr < 6
order by l, rbr

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.