Data Explorer averages by year?

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

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

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

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

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

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