Hi,
Let me confirm if this is what you need before setting up other queries:
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 |