How to do partition by using date intervals in SQL

I have the following sample data with 3 columns, i.e., account_id, interval_start, interval_end. And I want to create a reactivated column with the given output in the following table.

For each account_id, reactivated column should start with a 0 value and then I want to check if the previous interval_end and current interval_start value is more than a month then I want to increment that value as in to 1 and then next time when previous interval_end and current interval_start is more than a month, then it should be 2 and so on.

So far I'm only able to get 0s and 1s in the reactivated column by doing the following but its not what I want:

case when
interval_start - lag(interval_end) over (partition by account_id order by interval_start) as prev_interval_end > 30 then 1 else 0 end

How do I get the value to increment as well for each account_id please.

You want a running count of consecutive rows that overlap.

You are on the right track with lag(); you just need another level of windowing to sum those flags:

select t.*,
    sum(case when interval_start > lag_interval_end then 1 else 0 end) grp
from (
    select t.*,
        lag(interval_end) over (partition by account_id order by interval_start) as lag_interval_end
    from mytable t
) t

This is standard SQL, that should work on most databases that support window functions. Depending on your actual database, there might be neater ways to express the window sum, eg in Postgres:

count(*) filter(where interval_start > lag_interval_end) grp

Or in MySQL:

sum(interval_start > lag_interval_end) grp

In Redshift, we need a rows clause:

sum(case when interval_start > lag_interval_end then 1 else 0 end) over (
    partition by account_id 
    order by interval_start 
    rows unbounded preceding
) as  grp

Adding to above answer. Since I'm using Redshift what worked for me is:

sum(case when interval_start > lag_interval_end then 1 else 0 end) over (partition by account_id order by interval_start rows unbounded preceding) as  grp

Solution for PostgreSQL

Query with sample data

with dt as (
select * from (values
('ABC', DATE'2015-01-01'::timestamp,DATE'2015-02-01'::timestamp),
('ABC', DATE'2015-03-01',DATE'2015-04-01'),
('ABC', DATE'2015-09-01',DATE'2015-10-01'),
('ABC', DATE'2015-10-01',DATE'2015-11-01'),
('ABC', DATE'2017-04-01',DATE'2017-05-01'),
('ABC', DATE'2017-05-01',DATE'2017-06-01'),
('XXX', DATE'2015-01-01',DATE'2015-02-01')
) t(account_id, interval_start, interval_end)),
dt2 as (
select 
  account_id, interval_start, interval_end,
  case 
   interval_start - lag(interval_end) over(partition by account_id order by interval_start) >= interval '1 month'
  when true then 1
  else 0 end delta_act
from  dt)
select 
 account_id, interval_start, interval_end, delta_act,
 sum(delta_act) over(partition by account_id order by interval_start) reactivated
from dt2
order by 1,2

The subquery dt2 calculate the delta aktivation based on you one month inactivity rule.

  case 
   interval_start - lag(interval_end) over(partition by account_id order by interval_start) >= interval '1 month'
  when true then 1
  else 0 end delta_act  

Finally you must only sum this delta using the window function

 sum(delta_act) over(partition by account_id order by interval_start) reactivated

Result

account_id|interval_start     |interval_end       |delta_act|reactivated|
----------+-------------------+-------------------+---------+-----------+
ABC       |2015-01-01 00:00:00|2015-02-01 00:00:00|        0|          0|
ABC       |2015-03-01 00:00:00|2015-04-01 00:00:00|        0|          0|
ABC       |2015-09-01 00:00:00|2015-10-01 00:00:00|        1|          1|
ABC       |2015-10-01 00:00:00|2015-11-01 00:00:00|        0|          1|
ABC       |2017-04-01 00:00:00|2017-05-01 00:00:00|        1|          2|
ABC       |2017-05-01 00:00:00|2017-06-01 00:00:00|        0|          2|
XXX       |2015-01-01 00:00:00|2015-02-01 00:00:00|        0|          0|