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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f616c2e448ad930ee04ed8f5f30dbb30
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:
https://stackoverflow.com/a/46499873/939860
https://dba.stackexchange.com/questions/202677/update-value-in-timestamptz-column/202686#202686
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:
https://stackoverflow.com/a/7622046/939860
A multicolumn index on (id, start_date, end_date DESC) should help performance a lot. See:
https://dba.stackexchange.com/questions/39589/optimizing-queries-on-a-range-of-timestamps-two-columns/39599#39599
Alternatively, consider the SQL OVERLAPS operator, or (maybe best) tstzrange data types (and matching indexes). See:
https://stackoverflow.com/a/15305292/939860
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.
