Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this:

``````Order_year, Contact
2015, 123
2014, 123
2013, 123
2011, 123
2010, 123
2015, 234
2014, 234
2011, 234
``````

I want to calculate for each year how many consecutive order years each `contact_id` has. The problem is, there are gaps such as `contact_id = 123`, it has 2010-2011, then 2013-2015.

The output would be:

``````report_year, contact, consecutive_years
2015, 123, 3
2014, 123, 2
2013, 123, 1
2012, 123, 0
2011, 123, 2
2010, 123, 1
2015, 234, 3
2014, 234, 2
2013, 234, 1
2012, 234, 0
2011, 234, 1
``````

Or if you just want to calculate the max order year with the max consecutive years, that's fine, such as:

``````order_year, contact, consecutive_years
2015, 123, 3
2011, 123, 2
2015, 234, 2
``````

So I've built a short SQL script to do this operation, based on the original sample input data:

``````Order_year, Contact
2015, 123
2014, 123
2013, 123
2011, 123
2010, 123
2015, 234
2014, 234
2011, 234
``````

The script is as follows:

``````WITH RECURSIVE ranked_order AS(
SELECT order_year, contact, 1 AS counter FROM orders
UNION
SELECT orders.order_year, orders.contact, ranked_order.counter + 1
FROM orders JOIN ranked_order
ON orders.order_year-1 = ranked_order.order_year
WHERE orders.contact = ranked_order.contact
),
max_from_ranked_order AS(
SELECT order_year AS report_year, contact, max(counter) AS consecutive_year
FROM ranked_order
GROUP BY order_year, contact
),
start_and_consecutive_years AS(
SELECT  contact, max(consecutive_year) AS consecutive_years,
report_year - consecutive_year AS start_year
FROM max_from_ranked_order
GROUP BY contact, start_year)
SELECT *, start_year + consecutive_years AS end_year
FROM start_and_consecutive_years
ORDER BY contact;
``````

Now, for a little explanation:

Recusive Query

First, you need to find all the occasions of consecutive years, so I built the `ranked_order` table(maybe a bad name... maybe should have called it `counted_order`?) to count instances where a given contact ordered in back-to-back years.

Filter Results

So, due to the recursive query, I am left with all counted elements, rather than simply the maximum consecutive counts. So, I built `max_from_ranked_order` to extract the max counter value per `order_year,contact` grouping.

A little arithmetic...

This still doesn't quite yield what we need. Since we grouped over `order_year,contact`, you still end up with entries like

``````report_year, contact, consectuive_year
2015, 123, 3
2014, 123, 2
2013, 123, 1
2015, 234, 2
2014, 234, 1
etc...
``````

What you'll notice is that you have unique, information which you can group on by observing the starting year of the consecutive years. So, I created `start_and_consecutive_years` to take the maximum number of consecutive purchasing years for any given starting year, on a per customer basis.

Finally, I get my result by adding the `consecutive_years` to the `start_year`, and presto, the final `SELECT` yields:

``````contact, consecutive_years, start_year, end_year
123, 3, 2012, 2015
123, 2, 2009, 2011
234, 2, 2013, 2015
234, 1, 2010, 2011
``````

Note that it would be very easy to filter out for cases`WHERE consecutive years >= 2` to ignore "irrelevant" results.

Now for the caveats: I wrote this series of CTEs for ease of readability and explanation. There may be a more computationally or programatically efficient way to achieve your results, but on even a moderately sized table this should get you your results pretty quick.

To get all gapless time ranges per contact summed up:

``````SELECT max(order_year) AS order_year, contact, count(*) AS consecutive_years
FROM  (
SELECT *, order_year - row_number() OVER (PARTITION BY contact
ORDER BY order_year) AS grp
FROM   orders
) sub
GROUP  BY contact, grp
ORDER  BY contact, 1;
``````

To get just the longest run per contact, add `DISTINCT ON`:

``````SELECT DISTINCT ON (contact)
max(order_year) AS order_year, contact, count(*) AS consecutive_years
FROM  (
SELECT *, order_year - row_number() OVER (PARTITION BY contact
ORDER BY order_year) AS grp
FROM   orders
) sub
GROUP  BY contact, grp
ORDER  BY contact, count(*) DESC;
``````

Result:

``````order_year | contact | consecutive_years
-----------+---------+------------------
2015       | 123     | 3
2015       | 234     | 2
``````

SQL Fiddle.

The trick is to - per `contact` - subtract the running count (`row_number()`) from each year. Consecutive rows produce the same group number (`grp`). the number itself has no meaning, it just identifies groups per `contact`.

Then count, sort, get the maximum count. Voilá.

Details for `DISTINCT ON`:

Related answer with more explanation for the basic technique:

My solution with Oracle SQL:

``````with ranked as (
select c.contact, c.order_year, rank() over (partition by name order by year) rank from contest_prize c
),
ranked_2 as (
select r1.contact, r1.order_year, r1.rank  from ranked r1 inner join ranked r2 on r1.contact= r2.contact
where r2.order_year- r1.order_year= 1 and r2.rank - r1.rank = 1
),
ranked_3 as (
select r1.contact, r1.order_year, r1.rank  from ranked r1 inner join ranked r2 on r1.contact= r2.contact
where r2.order_year- r1.order_year= 2 and r2.rank - r1.rank = 2
)
select r.* from ranked r inner join ranked_3 r3 on r.contact= r3.contact and r.rank between r3.rank and r3.rank + 2;
``````