# Number of topics per tag

**URL:** <https://meta.discourse.org/t/number-of-topics-per-tag/139957>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [28 בינואר,‏ 2020,‏ 4:26pm UTC](https://meta.discourse.org/t/number-of-topics-per-tag/139957 "2020-01-28T16:26:29Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![tpetrov](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/tpetrov/32/164643_2.png) [@tpetrov](https://meta.discourse.org/u/tpetrov)\
**Post date:** [29 בינואר,‏ 2020,‏ 2:28pm UTC](https://meta.discourse.org/t/number-of-topics-per-tag/139957/3 "2020-01-29T14:28:55Z")

</div>

Awesome, thanks a lot for the hints.  
I was able to build it. Might not be perfect, but If anyone else finds it useful:

**New topics per tag**

```
-- [params]
-- int :months_ago = 0

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
    tags.name,
    count(1) as topic_count
FROM topics t
RIGHT JOIN topic_tags tt
    ON t.id = tt.topic_id
RIGHT JOIN tags tags
    ON tt.tag_id = tags.id
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 tt.topic_id IS NOT NULL
GROUP BY tags.name
ORDER BY topic_count DESC

```

---

_[View the full topic](https://meta.discourse.org/t/number-of-topics-per-tag/139957)._
