[Customer Contribution] Generate True Count From Start/End Date Range

This post is from one of our customers!

I work for a company that uses a subscription revenue model, where subscriptions are stored in postgres as start and end timestamps. For our insurance & billing department, it's important to know how many assets were actually out on any day of the week, but there are a couple issues:

The date ranges for a subscription are variable from 1 to 28+ days
The start and end timestamps can be retroactively changed by ops

If you're starting with a start and end timestamp (e.g. '2017-09-05' & '2017-09-15') and want to extrapolate all rows between those ('2017-09-05... '09-06'...'09-07'...'09-08'...)

Start by generating a table of dates in a given range,

FROM ( SELECT
  date( generate_series('2017-09-01'::timestamp, now(), '1 day') as days
) as all_dates

Then left join in your events table on both the start and end dates.

LEFT JOIN ( SELECT
  e.sub_id
  ,e.start --example: '2017-09-05'
  ,e.end --example: '2017-09-15'

  FROM events e
) as subs
  ON (subs.end >= all_dates.days) AND (subs.start <= all_dates.days)

This leaves you with an extrapolated table of all the days between the two dates for quick and easy aggregation.

Updated 03-02-2023

CDT