Cumulative running SUM for date ranges with gaps

Question

I am looking for assistance to get a running sum of the difference between two dates that are on separate rows.

Table looks like:

client_id	name	autopay_status	contract_id	start_date	end_date
1	Contract 1	Inactive	1111	"2019-08-30"	"2020-02-29"
1	Contract 1	Inactive	1112	"2020-02-29"	"2020-08-29"
1	Contract 1	Active	1113	"2020-08-29"	"2021-02-28"
2	Contract 2	Inactive	2221	"2019-08-30"	"2020-02-29"
2	Contract 2	Inactive	2222	"2020-02-29"	"2020-08-29"
2	Contract 2	Active	2223	"2020-08-29"	"2021-02-28"
3	Contract 3	Inactive	3331	"2019-08-30"	"2020-02-29"
3	Contract 3	Inactive	3332	"2020-03-29"	"2020-09-29"
3	Contract 3	Inactive	3333	"2020-09-29"	"2021-03-28"
3	Contract 3	Active	3334	"2021-03-28"	"2021-09-28"

I have a query that looks at the previous end_date and if it is within one day then that is a continuing contract.

SELECT
    case when
        (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
         else false
    end as continous_contract,
    end_date - start_date as contract_days,
    client_id,
    contract_id,
    autopay_status,
    start_date,
    end_date
FROM
    client_contracts
ORDER BY
    client_id, start_date

This adds two extra alias columns.

continuous_contract	contract_days	client_id	name	autopay_status	contract_id	start_date	end_date
true	183	3	Contract 3	Inactive	3331	"2019-08-30"	"2020-02-29"
false	184	3	Contract 3	Inactive	3332	"2020-03-29"	"2020-09-29"
true	183	3	Contract 3	Inactive	3333	"2020-09-29"	"2021-03-28"
true	182	3	Contract 3	Active	3334	"2021-03-28"	"2021-09-28"

My goal here is to sum up the days a client has had a continuous contract so table would look similar to below for the above example:

sum_days	continuous_contract	contract_days	client_id
183	true	183	3
184	false	184	3
367	true	183	3
549	true	182	3
733	true	184	3
181	false	181	3

I have put together the below query, but it only sums up the previous two values.

SELECT
*
FROM
(
    SELECT
        *,
        case
            when cc.continuous_contract = true then
                cc.contract_days + coalesce(lag(cc.contract_days) over (partition by cc.client_id), 1)
            else cc.contract_days
        end as added_contract_days
    FROM (
        SELECT
            case when
                (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
                 else false
            end as continuous_contract,
            end_date - start_date as contract_days,
            client_id,
            contract_id,
            autopay_status,
            start_date,
            end_date
        FROM
            client_contracts
        ) as cc
    ) as ccc

I am happy to change anything around to make this work.

Fiddle for assisting in seeing data and structure: Fiddle

Erwin Brandstetter · Accepted Answer · 2022-08-30T12:10:37.273

Can be done in three steps:

SELECT *, sum(contract_days) OVER (PARTITION BY client_id, contract_nr ORDER BY end_date) AS sum_days
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT continous_contract) OVER (PARTITION BY client_id ORDER BY end_date) AS contract_nr
   FROM  (
      SELECT client_id, start_date, end_date
           , start_date <= lag(end_date, 1, end_date) OVER (PARTITION BY client_id ORDER BY end_date) + 1 AS continous_contract
           , end_date - start_date AS contract_days  -- + 1 ???
      FROM   client_contracts
      ) sub1
   ) sub2
ORDER  BY client_id, start_date;

db<>fiddle here

The inner subquery sub1 is basically what you started with, simplified.
lag() optionally takes 3 arguments, the third being the fallback if no row is found.

sub2 adds a contract_nr for each continuous group of rows: every gap in the contract interval starts a new contract.

The outer SELECT finally adds the running sum.

This assumes that contracts never overlap per client.

See:

Select longest continuous sequence

Aside: end_date - start_date AS contract_days looks like an off-by-one error? If lower and upper bound shall be included, add + 1. (Of course, overlapping bounds are counted twice then.)

Cumulative running SUM for date ranges with gaps

1 Answers1