Data Explorer médias por ano?

Comunidade Dados e relatórios
1

Preciso criar esta consulta SQL do Data-Explorer:

Preciso fazer a solicitação por ano. Como entrada… por exemplo, de 2019-01-01 a 2019-12-31

Número de respostas por categoria (TÓPICO e POST)
Principais tags em um ano
Número médio de novos tópicos por mês (encontrei isso no painel de relatórios de administração)
Número médio de respostas por mês
Número médio de novos usuários por mês

Alguém pode me ajudar com isso??? Ou me dar alguma direção

Atenciosamente.

2

@michebs provavelmente pode te ajudar aqui.

5

Olá,

Deixe-me confirmar se é isso que você precisa antes de configurar outras consultas:

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
6

@michebs.. Sim, exatamente, você entendeu muito rápido

se não for pedir demais, seria algo assim

ano mês avg_topic

2020 01 número
2020 02 número
2020 03 número
e assim por diante…

e Obrigado antecipadamente por isso

Atenciosamente.,

7

Sem problema, acredito que esta seja a informação que você está procurando:

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

Vou configurar outras consultas com base nesse paradigma. :wink:

8

Olá,

Peço desculpas pelo atraso na resposta. Seguem as consultas solicitadas; como algumas são mais complexas que o habitual, estou à disposição caso tenha alguma dúvida.

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  --> *** Ativar para excluir Postagem sem categoria ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
year id category qt
2020 13 Geral 14
2020 16 Base de Conhecimento 3
2020 15 Equipe 3
2020 1 Sem categoria 2
2020 17 Ideias 1
2019 18 Construções 10
2019 1 Sem categoria 8
2019 11 CS001x: Introdução à Ciência da Computação 7
2019 13 Geral 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  --   *** Escolher o limite de classificação ***
ORDER BY year DESC, qt DESC
year rank tag_name quantity
2020 1 destaque 7
2020 2 recursos-humanos 3
2020 3 demo 1
2019 1 demo 12
2019 2 recursos-humanos 4
2019 3 destaque 3
2019 3 cliente 3
2019 3 marcos-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
9

Funciona perfeitamente…

Você salva meus dias…

Atenciosamente…