Get topic count per month for a certain category

Data & reporting
1

Maybe anyone more used to using Data explorer can help me out here?

I want to get a topic count per month statistic for a certain category. Would be good enough if it was for one year from the day it’s running.

Thanks in advance for helping.

2

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. :+1:

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. :slightly_smiling_face: 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
1 Like