Data Explorer averages by year?

Data & reporting
1

I need to create this Data-Explorer SQL Query:

I need to do the Request by Year. as Input … by example 2019-01-01 thru 2019-12-31

Number of Reply's by Category (TOPIC and POST)
top tags in a year
average number of new topics per month (I found this in admin report admin panel)
average number of reply per month
average number of new users per month

Someone Can give a Hand on this??? or give me some direction

Regards.,

2 Likes
2

@michebs can probably help you out here.

5 Likes
5

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
12 Likes
6

@michebs… Yes exactly, you got it very fast

if it’s not too much to ask, would be something like this

year month avg_topic

2020 01 number
2020 02 number
2020 03 number
etc…

and Thx in advanced for this

Regards.,

1 Like
7

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. :wink:

9 Likes
8

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
12 Likes
9

works like a charm,

you save my days…

Regards…

3 Likes