Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE task_states (
  id INT PRIMARY KEY,
  label TEXT NOT NULL
);
INSERT INTO task_states (id, label) VALUES
(1, 'Assigns'),
(2, 'In Progress'),
(3, 'Completed');
3 rows affected

CREATE TABLE tasks (
  id INT PRIMARY KEY,
  title TEXT NOT NULL,
  state_id INT REFERENCES task_states,
  inserted_at TIMESTAMP NOT NULL
);
INSERT INTO tasks (id, title, state_id, inserted_at) VALUES
(1, 'First Task', 1, '2022-05-05 19:16:44'),
(2, 'Second Task', 1, '2022-05-07 18:54:40'),
(3, 'Third Task', 1, '2022-05-07 19:18:28'),
(4, 'Fourth Task', 1, '2022-05-10 15:28:57');
4 rows affected

CREATE TABLE task_logs (
  id INT PRIMARY KEY,
  event TEXT NOT NULL,
  target TEXT NOT NULL,
  value TEXT NOT NULL,
  task_id INT REFERENCES tasks,
  inserted_at TIMESTAMP NOT NULL
);
INSERT INTO task_logs (id, event, target, value, task_id, inserted_at) VALUES
(1, 'changed', 'state', 'Assigns', 1, '2022-05-05 19:16:44'),
(2, 'changed', 'state', 'In Progress', 1, '2022-05-06 11:43:14'),
(3, 'changed', 'state', 'Assigns', 2, '2022-05-07 18:54:40'),
(4, 'changed', 'state', 'Assigns', 3, '2022-05-07 19:18:28'),
(5, 'changed', 'state', 'Completed', 1, '2022-05-08 12:11:38'),
(6, 'changed', 'state', 'In Progress', 2, '2022-05-09 09:22:53'),
(7, 'changed', 'state', 'Assigns', 4, '2022-05-10 15:28:57'),
(8, 'changed', 'state', 'Completed', 2, '2022-05-11 11:21:53'),
(9, 'changed', 'state', 'In Progress', 3, '2022-05-11 17:42:02');
9 rows affected

-- gets # of tasks in each state ON '2022-05-10'
SELECT date('2022-05-10'), state.id as state_id, state.label, count(sub.id)
FROM (
   SELECT DISTINCT ON (t.id) t.id, logs.value
   FROM tasks t
   INNER JOIN task_logs logs ON logs.task_id = t.id
   WHERE date(logs.inserted_at) <= date('2022-05-10') AND logs.target = 'state'
   ORDER BY t.id, logs.inserted_at DESC
) sub
RIGHT JOIN task_states state ON state.label = sub.value
GROUP BY state.id
ORDER BY state.id;

-- gets # of tasks in each state ON '2022-05-11'
SELECT date('2022-05-11'), state.id as state_id, state.label, count(sub.id)
FROM (
   SELECT DISTINCT ON (t.id) t.id, logs.value
   FROM tasks t
   INNER JOIN task_logs logs ON logs.task_id = t.id
   WHERE date(logs.inserted_at) <= date('2022-05-11') AND logs.target = 'state'
   ORDER BY t.id, logs.inserted_at DESC
) sub
RIGHT JOIN task_states state ON state.label = sub.value
GROUP BY state.id
ORDER BY state.id;

-- DESIRED RESULT IS SOMETHING SIMILAR TO THE QUERY ABOVE REPRODUCED
-- FOR EACH DATE IN A SERIES OF DATES


date
state_id
label
count


2022-05-10
1
Assigns
2

2022-05-10
2
In Progress
1

2022-05-10
3
Completed
1




date
state_id
label
count


2022-05-11
1
Assigns
1

2022-05-11
2
In Progress
1

2022-05-11
3
Completed
2


-- DESIRED RESULT: 
-- | date       | state_id | label       | count |
-- | ---------- | -------- | ----------- | ----- |
-- | 2022-05-05 | 1        | Assigns     | 1     |
-- | 2022-05-05 | 2        | In Progress | 0     |
-- | 2022-05-05 | 3        | Complete    | 0     |
-- | 2022-05-06 | 1        | Assigns     | 0     |
-- | 2022-05-06 | 2        | In Progress | 1     |
-- | 2022-05-06 | 3        | Complete    | 0     |
-- | 2022-05-07 | 1        | Assigns     | 2     |
-- | 2022-05-07 | 2        | In Progress | 1     |
-- | 2022-05-07 | 3        | Complete    | 0     |
-- | 2022-05-08 | 1        | Assigns     | 2     |
-- | 2022-05-08 | 2        | In Progress | 0     |
-- | 2022-05-08 | 3        | Complete    | 1     |
-- | 2022-05-09 | 1        | Assigns     | 1     |
-- | 2022-05-09 | 2        | In Progress | 1     |
-- | 2022-05-09 | 3        | Complete    | 1     |
-- | 2022-05-10 | 1        | Assigns     | 2     |
-- | 2022-05-10 | 2        | In Progress | 1     |
-- | 2022-05-10 | 3        | Complete    | 1     |
-- | 2022-05-11 | 1        | Assigns     | 1     |
-- | 2022-05-11 | 2        | In Progress | 1     |
-- | 2022-05-11 | 3        | Complete    | 2     |
date	state_id	label	count
2022-05-10	1	Assigns	2
2022-05-10	2	In Progress	1
2022-05-10	3	Completed	1
date	state_id	label	count
2022-05-11	1	Assigns	1
2022-05-11	2	In Progress	1
2022-05-11	3	Completed	2