I think you should be able to cannibalise some of the examples in What cool data explorer queries have you come up with? to get you close (and the sql-query tag has lots more topics too)
If you have a look through those and see if anything chimes with what you’re after, and we can help you refine anything if you need it.
I’m sure there are slicker ways to do it, but something maybe like this?
-- [params]
-- int :cat_id = 6
-- date :date_from = 01/03/2022
-- date :date_to = 01/04/2022
SELECT t.category_id, count(t.category_id)
FROM topics t
WHERE t.category_id = :cat_id
AND t.created_at::date BETWEEN :date_from::date AND :date_to::date
AND t.deleted_at is null
GROUP BY t.category_id
Hang on. That wouldn’t be a month-by-month. Let me have another go at it…
Perhaps I’ll fare better with this one. How about something like this?
-- [params]
-- int :cat_id = 5
-- int :months = 12
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(category_id) AS "new_topics_month"
FROM topics t
WHERE t.category_id = :cat_id
AND t.deleted_at is NULL
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) DESC, date_part('month', created_at) DESC
LIMIT :months