SQL (Redshift) – Find consecutive numbers

Suppose I have a table below:

ID  NUM
 1   1
 2   1
 3   1
 4   2
 5   3 
 6   1

I want to find which number(s) appears 3 times in a row. So in this example the output is 1.

How can I achieve this using regular SQL (not PL/SQL), when I have way much more rows in the data?

Thanks!

Answer

This should work. here is the sqlfiddle

select
    distinct num as ConsecutiveNums
from
(
    select
        num,
        count(*) as total
    from
    (
        select
            num,
            id - row_number() over (partition by num order by id) as rnk
        from myTable
    ) t
    group by
        num,
        rnk
) tt
where total > 2