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

<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:** [16 במאי,‏ 2020,‏ 8:55pm UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/8 "2020-05-16T20:55:02Z")

</div>

Hi,

Sorry for the delay in responding, follow the requested queries, as some are more complex than usual, I am available if you have any questions.

Michelle

> [@hhlp](#):
>
> `Number of Reply's by Category (TOPIC and POST)`

```
WITH post AS (SELECT 
    id AS post_id,
    topic_id,
    EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
    AND deleted_at ISNULL
    AND post_number != 1)
    
SELECT 
    p.year,
    t.category_id AS id, 
    c.name category,
    COUNT(p.post_id) AS qt
FROM post p
INNER JOIN topics t ON t.id = p.topic_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at ISNULL
-- AND t.category_id NOT NULL --> ***Enable to delete Post without category***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC

```

| year | id | category | qt |
| --- | --- | --- | --- |
| 2020 | 13 | General | 14 |
| 2020 | 16 | Knowledge Base | 3 |
| 2020 | 15 | Staff | 3 |
| 2020 | 1 | Uncategorized | 2 |
| 2020 | 17 | Ideas | 1 |
| 2019 | 18 | Builds | 10 |
| 2019 | 1 | Uncategorized | 8 |
| 2019 | 11 | CS001x: Intro to Computer Science | 7 |
| 2019 | 13 | General | 5 |

* * *

> [@hhlp](#):
>
> `top tags in a year`

```
WITH data AS (SELECT 
    tag_id,
    EXTRACT(YEAR FROM created_at) AS year
FROM topic_tags)

SELECT year, rank, name, qt FROM (
    SELECT 
        tag_id,
        COUNT(tag_id) AS qt,
        year,
        rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank    
    FROM
        data
    GROUP BY year, tag_id) as rnk
INNER JOIN tags ON tags.id = rnk.tag_id
WHERE rank <= 5 -- ***Choose the ranking limit***
ORDER BY year DESC, qt DESC

```

| year | rank | tag\_name | quantity |
| --- | --- | --- | --- |
| 2020 | 1 | featured | 7 |
| 2020 | 2 | human-resources | 3 |
| 2020 | 3 | demo | 1 |
| 2019 | 1 | demo | 12 |
| 2019 | 2 | human-resources | 4 |
| 2019 | 3 | featured | 3 |
| 2019 | 3 | customer | 3 |
| 2019 | 3 | milestones-2019 | 3 |

* * *

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

```
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
WHERE deleted_at ISNULL)

    SELECT
        year,
        month,
        ROUND(AVG(qt_topic_day)) AS avg_topic_by_day
    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\_by\_day |
| --- | --- | --- |
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 2 |
| 2020 | 4 | 3 |
| 2020 | 5 | 2 |
| 2019 | 4 | 9 |
| 2019 | 5 | 4 |
| 2019 | 6 | 4 |
| 2019 | 7 | 1 |
| 2019 | 8 | 2 |
| 2019 | 9 | 3 |
| 2019 | 10 | 1 |

* * *

> [@hhlp](#):
>
> `average number of reply per month`

```
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 posts
WHERE post_type = 1
    AND deleted_at ISNULL
    AND post_number != 1)

SELECT
    year,
    month,
    ROUND(AVG(qt_reply_day)) AS avg_reply_by_day
FROM
(SELECT COUNT(id) AS qt_reply_day,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_reply
GROUP BY month, year
ORDER BY year DESC, month ASC 

```

| year | month | avg\_reply\_by\_day |
| --- | --- | --- |
| 2020 | 1 | 7 |
| 2020 | 3 | 2 |
| 2020 | 4 | 5 |
| 2020 | 5 | 6 |
| 2019 | 4 | 3 |
| 2019 | 5 | 2 |
| 2019 | 6 | 4 |
| 2019 | 7 | 2 |
| 2019 | 8 | 15 |
| 2019 | 9 | 3 |
| 2019 | 10 | 5 |
| 2019 | 12 | 2 |

* * *

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

```
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 users)

SELECT
    year,
    month,
    ROUND(AVG(qt_new_user)) AS avg_new_user_by_day
FROM
(SELECT COUNT(id) AS qt_new_user,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_new_user
GROUP BY month, year
ORDER BY year DESC, month ASC 

```

| year | month | avg\_new\_user\_by\_day |
| --- | --- | --- |
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 1 |
| 2020 | 4 | 3 |
| 2020 | 5 | 1 |
| 2019 | 4 | 4 |
| 2019 | 5 | 2 |
| 2019 | 6 | 2 |
| 2019 | 7 | 1 |

* * *

---

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