Count per day per category using generate

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.

CodePudding user response：

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.