Sql 一系列日期中每个日期的分组聚合计数

我试图使用下表，在series个日期中，将task个计数按state分组:

tasks
-----
| id | title       | state_id | inserted_at         |
| -- | ----------- | -------- | ------------------- |
| 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 |

task_states
-----
| id | label      |
| -- | ---------- |
| 1  | Assigns    |
| 2  | In Process |
| 3  | Completed  |

task_logs
-----
| id | event   | target | value      | task_id | inserted_at        |
| -- | ------- | ------ | ---------- | ------- | -------------------|
| 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|

每个任务没有一致的每日"状态"记录，因为task_logs只有任务更改状态时的条目.这意味着我必须在指定日期之前获取每个任务的最后一个"状态更改"日志(log).我使用以下查询获取一天前每个状态的任务计数:

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;
------------------
| date       | state_id | label      | count |
| ---------- | -------- | ---------- | ----- |
| 2022-05-10 | 1        | Assigns    | 2     |
| 2022-05-10 | 2        | In Process | 1     |
| 2022-05-10 | 3        | Completed  | 1     |

我的问题是，试图将上面的查询与generate_series相结合，以获得一系列日期的daily计数，例如:

| 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     |

这是一个dbfiddle的设置，上面的表格.对于如何在一系列日期(generate_series(current_date - interval '5 day', current_date, '1 day'))中的每个日期执行上述查询(或重写)的任何 idea / idea ，我们将不胜感激！