# Data Explorer averages by year?

**URL:** <https://meta.discourse.org/t/data-explorer-averages-by-year/150424>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [5 במאי,‏ 2020,‏ 10:55am UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424 "2020-05-05T10:55:26Z")\
**Posts on this page:** 1\
**Showing post:** 5

<div class="post-metadata">

**Author:** ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\
**Post date:** [7 במאי,‏ 2020,‏ 10:26am UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/5 "2020-05-07T10:26:59Z")

</div>

Hi,

Let me confirm if this is what you need before setting up other queries:

> [@hhlp](#):
>
> average number of new topics per month

```
WITH data AS (SELECT 
    id,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM topics)

SELECT
    ROUND(AVG(qt_topic_month)) AS avg_topic,
    year
FROM
(SELECT COUNT(id) AS qt_topic_month,
        month,
        year
FROM data    
GROUP BY month, year) AS top_m
GROUP BY year
ORDER BY year DESC

```

| avg\_topic | year |
| --- | --- |
| 694 | 2020 |
| 1011 | 2019 |
| 284 | 2018 |
| 79 | 2017 |

---

_[View the full topic](https://meta.discourse.org/t/data-explorer-averages-by-year/150424)._
