Tabibitosan Method: Example 6
Get count of latest consecutive years per group

SQL>  create table ex8 as(
  2   select 1 as ID,2009 as yr from dual union
  3   select 1,2008 from dual union
  4   select 1,2007 from dual union
  5   select 1,2006 from dual union
  6   select 1,2005 from dual union
  7   select 1,2004 from dual union
  8   select 1,2002 from dual union
  9   select 1,2001 from dual union
 10   select 2,2009 from dual union
 11   select 2,2008 from dual union
 12   select 2,2005 from dual);

Table created.

SQL> select * from ex8;

        ID         YR
---------- ----------
         1       2001
         1       2002
         1       2004
         1       2005
         1       2006
         1       2007
         1       2008
         1       2009
         2       2005
         2       2008
         2       2009

11 rows selected.

Expected output:

1    6
2    2

SQL> select
  2   yr - row_number() over ( partition by id order by yr)
  3  from ex8;

YR-ROW_NUMBER()OVER(PARTITIONBYIDORDERBYYR)
-------------------------------------------
                                       2000
                                       2000
                                       2001
                                       2001
                                       2001
                                       2001
                                       2001
                                       2001
                                       2004
                                       2006
                                       2006

11 rows selected.

SQL> select  id
  2    yr,
  3    yr - row_number() over ( partition by id order by yr) seq
  4  from ex8;

        YR        SEQ
---------- ----------
         1       2000
         1       2000
         1       2001
         1       2001
         1       2001
         1       2001
         1       2001
         1       2001
         2       2004
         2       2006
         2       2006

11 rows selected.

SQL> select id,count(*) keep (dense_rank last order by grp) cnt
  2  from (
  3    select  id,
  4      yr,
  5      yr - row_number() over ( partition by id order by yr) grp
  6    from ex8)
  7  group by id;

        ID        CNT
---------- ----------
         1          6
         2          2