SQL query display # of events per day between two time ranges

Question

I have the following table:

id      start_date                 end_date                    status
----------------------------------------------------------------------
1a      2020-04-09 21:34:00.000    2020-04-13 15:46:52.000      green
1a      2020-04-13 15:46:52.000    2020-04-16 15:46:52.000      red
1a      2020-04-16 15:46:52.000    null                         yellow
2a      2020-04-09 20:33:00.000    2020-04-16 15:46:52.000      green
2a      2020-04-16 15:46:52.000    2020-04-21 15:46:52.000      red
2a      2020-04-21 15:46:52.000    null                         yellow
3a      2020-04-09 21:34:00.000    2020-04-09 22:34:00.000      green
3a      2020-04-09 22:34:00.000    2020-04-09 23:36:00.000      red
3a      2020-04-09 23:36:00.000    2020-04-09 23:38:00.000      yellow
3a      2020-04-09 23:38:00.000    null                         red

I am interested to count how many unique ids were in each state everyday between two time ranges. So I need to generate days between two time ranges as a starter. For example, item with id 1a was green everyday from 2020-04-09 21:34:00.000 to 2020-04-13 15:46:52.000 and I need to count that.

Edit: The most important thing is that if one item changes several states on the same day, I need to count it only once for the most recent state on a day. For example, item with id 3a changed states several times on 2020-04-09. However, I only care about the most recent state which is red (on 2020-04-09 23:38:00.000). This is to make sure that I don't artificially inflate the data.

My desired output is this:

day           state          total       ids
2020-04-09    green          2           [1a, 2a]
2020-04-09.   yellow         0           []
2020-04-09.   red            1           [3a]
2020-04-10.   green          2           [1a, 2a]
2020-04-10.   yellow         0           []
.....etc         

I have this query:

 select serie.day as time,
 a.state,
 count(a.start_date) as total,
 a.id
 from (
 SELECT date_series::date AS day
    FROM generate_series(
        '2020-04-09 00:00:00.000'::date,
        '2020-04-24 15:22:22.000',
        '1 day'
    ) AS date_series
 ) as serie
 left join event_entries a on a.start_date::date = serie.day::date 
 group by serie.day, a.state, a.id order by time

But it only records the total # ids per state on the start_date and nothing in between. How can I refactor this query to produce the desired result?

Here is the fiddle

Answer 1

The following solution assumes that most IDs (event_entries.id) have entries for most days. For different data distributions, different solutions will be (much) faster. You didn't specify.

Full solution:

SELECT (the_day AT TIME ZONE 'UTC')::date AS "day"  -- UTC days
     , state
     , COALESCE(e.ct, 0) AS total
     , COALESCE(e.ids, '{}') AS ids
FROM  (
   SELECT d.the_day, a.state, count(*) AS ct, array_agg(i.id) AS ids
   FROM   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                        , timestamptz '2020-04-24 15:22:22+0'
                        , interval    '1 day') d(the_day)
   CROSS  JOIN (SELECT DISTINCT id FROM event_entries) AS i  -- there are faster ways
   CROSS  JOIN LATERAL (
      SELECT state
      FROM   event_entries a
      WHERE  a.id = i.id
      AND    a.start_date <  d.the_day + interval '1 day'
      AND   (a.end_date   >= d.the_day OR a.end_date IS NULL)
      ORDER  BY a.end_date DESC  -- NULLS FIRST is the default we need
      LIMIT  1
      ) a
   GROUP  BY d.the_day, a.state
   ) e
RIGHT JOIN (
   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                 , timestamptz '2020-04-24 15:22:22+0'
                 , interval    '1 day') d(the_day)
   CROSS JOIN (SELECT DISTINCT state FROM event_entries) s -- there are faster ways
   ) e0  USING (the_day, state)
ORDER  BY the_day, state;

db<>fiddle here

The core solution is:

SELECT (d.the_day AT TIME ZONE 'UTC')::date AS "day"  -- UTC days
     , a.state
     , count(*) AS ct
     , array_agg(i.id) AS ids
FROM   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                     , timestamptz '2020-04-24 15:22:22+0'
                     , interval    '1 day') d(the_day)
CROSS  JOIN (SELECT DISTINCT id FROM event_entries) AS i  -- there are faster ways
CROSS  JOIN LATERAL (
   SELECT state
   FROM   event_entries a
   WHERE  a.id = i.id
   AND    a.start_date <  d.the_day + interval '1 day'
   AND   (a.end_date   >= d.the_day OR a.end_date IS NULL)
   ORDER  BY a.end_date DESC  -- NULLS FIRST is the default we need
   LIMIT  1
   ) a
GROUP  BY d.the_day, a.state
ORDER  BY d.the_day, a.state;

RIGHT JOIN that to a Cartesian product of all states and days to include missing states with a total of 0, and arrive at the full solution above.

Explanation

The core solution starts with a Cartesian Product (CROSS JOIN) of all days of interest (d) and all IDs (i). Note the adapted generate_series() expression. Since your table holds timestamptz, it's simplest and fastest to work with timestamptz all the way. This also fixes the widespread, sneaky corner-case error with dates silently depending on the current TimeZone setting of the session. I assumed UTC days. Replace with your time zone. See:

• Generating time series between two dates in PostgreSQL
• Update value in timestamptz column

To work with standard calendar days, the first argument to generate_series() should have a 00:00 time component. You can adapt to shift days any way you like.

Since the same derived table generated with generate_series() is used twice, we might break that out into a CTE. But generate_series() is so fast that the overhead hardly pays.

The LATERAL subquery a then picks the latest ( = effective) state for each of these combinations. The special case end_date IS NULL counts as latest entry. Since NULL sorts first in descending order, this falls in line effortlessly. See:

• Sort NULL values to the end of a table

A multicolumn index on (id, start_date, end_date DESC) should help performance a lot. See:

• Optimizing queries on a range of timestamps (two columns)

Alternatively, consider the SQL OVERLAPS operator, or (maybe best) tstzrange data types (and matching indexes). See:

• Find overlapping date ranges in PostgreSQL

In the outer SELECT, GROUP BY & ORDER BY to arrive at your counts.

I added the not "there are faster ways" two times for subqueries that should really be replaced with tables readily holding unique rows for (much) better performance. You didn't specify what's at our disposal.