Generating counts of open tickets over time, given opened and closed dates

Question

I have a set of data for some tickets, with datetime of when they were opened and closed (or NULL if they are still open).

+------------------+------------------+
|    opened_on     |    closed_on     |
+------------------+------------------+
| 2019-09-01 17:00 | 2020-01-01 13:37 |
| 2020-04-14 11:00 | 2020-05-14 14:19 |
| 2020-03-09 10:00 | NULL             |
+------------------+------------------+

We would like to generate a table of data showing the total count of tickets that were open through time, grouped by date. Something like the following:

+------------------+------------------+
|    date          |    num_open      |
+------------------+------------------+
| 2019-09-01 00:00 | 1                |
| 2020-09-02 00:00 | 1                |
| etc...           |                  |
| 2020-01-01 00:00 | 0                |
| 2020-01-02 00:00 | 0                |
| etc...           |                  |
| 2020-03-08 00:00 | 0                |
| 2020-03-09 00:00 | 1                |
| etc...           |                  |
| 2020-04-14 00:00 | 2                |
+------------------+------------------+

Note that I am not sure about how the num_open is considered for a given date - should it be considered from the point of view of the end of the date or the start of it i.e. if one opened and closed on the same date, should that count as 0?

This is in Postgres, so I thought about using window functions for this, but trying to truncate by the date is making it complex. I have tried using a generate_series function to create the date series to join onto, but when I use the aggregate functions, I've "lost" access to the individual ticket datetimes.

Answer 1

You can use generate_series() to build the list of dates, and then a left join on inequality conditions to bring the table:

select s.dt, count(t.opened_on) num_open
from generate_series(date '2019-09-01', date '2020-09-01', '1 day') s(dt)
left join mytable t
    on s.dt >= t.opened_on and s.dt < coalesce(t.closed_on, 'infinity')
group by s.dt

Actually, this seems a bit closer to what you want:

select s.dt, count(t.opened_on) num_open
from generate_series(date '2019-09-01', date '2020-09-01', '1 day') s(dt)
left join mytable t
    on s.dt >= t.opened_on::date and s.dt < coalesce(t.closed_on::date, 'infinity')
group by s.dt