Search code examples
sqlpostgresqlaggregate-functions

Count units by active and inactive dates

I have a table with the following structure

table
unit      date_active  date_inactive
a         2018-01-01   NULL
b         2018-01-01   2020-07-05
c         2019-02-01   2020-01-01

I need a query to generate the total count of active units by month from date first active to today. The output needs to be along the lines of:

2018-01-01 2
2018-02-01 2
....

When date inactive is NULL of course the unit is still active today. I'm using PostgreSQL

Solution

  • Generate series of dates, join it to the table and count units for each date:

    select m::date as month, count(unit)
from generate_series('2018-01-01'::date, '2020-12-31', '1 month') m
left join my_table on m::date between date_active and coalesce(date_inactive, 'infinity')
group by 1
order by 1

    Db<>Fiddle.