# 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:** 7

<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,‏ 4:08pm UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/7 "2020-05-07T16:08:01Z")

</div>

No problem, I believe this is the information you are looking for:

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

SELECT
    year,
    month,
    ROUND(AVG(qt_topic_day)) AS avg_topic
FROM
(SELECT COUNT(id) AS qt_topic_day,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC 

```

| year | month | avg\_topic |
| --- | --- | --- |
| 2020 | 1 | 23 |
| 2020 | 2 | 26 |
| 2020 | 3 | 24 |
| 2020 | 4 | 35 |
| 2020 | 5 | 31 |

I will set up other queries based on this paradigm. 😉

---

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