Calculate number of consecutive years (not gapless)

Question

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

score 3 · Answer 1 · answered Jun 18 '15 at 18:38

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 casesWHERE 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.

score 2 · Answer 2 · edited May 23 '17 at 11:33

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:

Select first row in each GROUP BY group?

Related answer with more explanation for the basic technique:

Select longest continuous sequence

score 0 · Answer 3 · answered Sep 09 '22 at 14:45

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;

Calculate number of consecutive years (not gapless)

3 Answers3

Recusive Query

Filter Results

A little arithmetic...