Count per day per category using generate_series()

Question

I am trying to generate using generate_series() for each day and each category, the count, in a given date range.

Table Posts:

id	date	category_id
1	2022-01-01	1
2	2022-01-01	1
3	2022-01-02	1
4	2022-01-02	2

Table Categories:

id	code
1	WEB
2	MOBILE
3	DESKTOP

Expected Results :

day	code	count
2022-01-01	WEB	2
2022-01-01	MOBILE	0
2022-01-01	DESKTOP	0
2022-01-02	WEB	1
2022-01-02	MOBILE	1
2022-01-02	DESKTOP	0
2022-01-03	WEB	0
2022-01-03	MOBILE	0
2022-01-03	DESKTOP	0
2022-01-04	WEB	0
2022-01-04	MOBILE	0
2022-01-04	DESKTOP	0
2022-01-05	WEB	0
2022-01-05	MOBILE	0
2022-01-05	DESKTOP	0

So far I have :

SELECT day::date, code, count(p.id)
FROM generate_series('2022-01-01'::date, '2022-01-05'::date, '1 DAY') AS day
CROSS JOIN categories c
LEFT JOIN posts p ON p.category_id = c.id
WHERE date BETWEEN '2022-01-01' AND '2022-01-05'
GROUP BY (day, code)
ORDER BY day;

The results is not quite there, I have some intuition that I should join on a sub-query but I'm not sure.

Link to fiddle

Thanks for your help.

Answer 1

You can first find the counts for each category per day, and then join the results onto the series:

select d::date, c.code, coalesce(t.c, 0) 
from generate_series('2022-01-01'::date, '2022-01-05'::date, '1 day') d 
cross join categories c 
left join (select p.date, p.category_id, count(*) c 
   from posts p group by p.date, p.category_id) t 
on c.id = t.category_id and t.date = d::date

See fiddle.