New topics per category (by month)
Returns the number of new topics created in each category for a given month, ordered by topic_count. The query accepts a ‘months ago’ parameter, defaults to 0 to give the results for the current month.
-- [params]
-- int :months_ago = 1
WITH query_period as (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)
SELECT
t.category_id,
count(1) as topic_count
FROM topics t
RIGHT JOIN query_period qp
ON t.created_at >= qp.period_start
AND t.created_at <= qp.period_end
WHERE t.user_id > 0
AND t.category_id IS NOT NULL
GROUP BY t.category_id
ORDER BY topic_count DESC