Get topic count per month for a certain category

Community Building Data & reporting

AI-generated summary

The user helmi requests help in creating an SQL query to get the topic count per month for a specific category over one year. JammyDodger suggests using the Data Explorer and provides a refined SQL query that groups topics by year and month, filtering by category ID and excluding deleted topics. The query uses parameters for category ID and the number of months, returning the count of new topics per month in descending order.

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 (Superseded) 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