# Top 50 active topics (and their associated categories)

**URL:** https://meta.discourse.org/t/top-50-active-topics-and-their-associated-categories/275110
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [12.Февраль.2018 11:15:00 UTC](https://meta.discourse.org/t/top-50-active-topics-and-their-associated-categories/275110 "2018-02-12T11:15:00Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![vinothkannans](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/vinothkannans/32/86465_2.png) [@vinothkannans](https://meta.discourse.org/u/vinothkannans)
#### Post date: [12.Февраль.2018 11:15:00 UTC](https://meta.discourse.org/t/top-50-active-topics-and-their-associated-categories/275110/1 "2018-02-12T11:15:00Z")

</div>

### Top 50 active topics (and their associated categories)

Returns the top 50 active topics per month. It’s based on the number of replies created for a topic in a given month. The query accepts a ‘months\_ago’ parameter, defaults to 0 to give results for the current month.

```sql
-- [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.id as topic_id,
t.category_id,
COUNT(p.id) as reply_count
FROM topics t
JOIN posts p
ON t.id = p.topic_id
JOIN query_period qp
ON p.created_at >= qp.period_start
AND p.created_at <= qp.period_end
WHERE t.archetype = 'regular'
AND t.user_id > 0
GROUP BY t.id
ORDER BY COUNT(p.id) DESC, t.score DESC
LIMIT 50

```