Skip to main content
typos
Source Link
Erwin Brandstetter
  • 186.6k
  • 28
  • 465
  • 639

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 addadds 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:

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

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 add 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:

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

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:

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

Source Link
Erwin Brandstetter
  • 186.6k
  • 28
  • 465
  • 639

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 add 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:

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