Consulta do Data Explorer para % de tópicos resolvidos por grupo de usuários específico

[Konrad_Sopala]

Olá a todos que estão usando o Data Explorer!

(Seja paciente comigo, @michebs - só farei mais duas perguntas, espero que outras pessoas também se beneficiem)

Alguém já criou uma consulta no Data Explorer para a porcentagem de tópicos resolvidos por grupo específico, por mês e ano?

Assim, o resultado será assim, mas a última coluna será a porcentagem

[sam (Sam Saffron)]

Podemos certamente fazer algo assim, todos os dados existem.

“% resolvido”… o que exatamente você quer dizer.

• A postagem foi feita em janeiro
• O grupo “awesome” obteve uma resposta aceita em fevereiro
• 73 outros tópicos tiveram soluções em fevereiro por outros grupos e usuários.

Então, acho que o “grupo awesome” tem uma taxa de resolução de 1,35% para fevereiro?

[Konrad_Sopala]

Digamos que haja 100 soluções em um mês. Existem vinte pessoas que pertencem a algum grupo e as pessoas começaram a marcar suas respostas como soluções, totalizando vinte delas. Quero uma consulta em que eu especifique o ID do grupo principal delas no script e obtenha os dados por mês para mostrar, neste mês, 20/100 - 20%.

[michebs (Michelle)]

Olá Konrad, desculpe pelo atraso.
Ainda estou validando algumas consultas, mas vou aproveitar para confirmar: este é o resultado que você está aguardando?

ano	mês	group_name	tt_groups	total	%
2021	1	team1	40	70	57
2021	1	team2	30	70	43

[michebs (Michelle)]

Tentei tornar as consultas mais detalhadas para facilitar a compreensão e a manutenção no futuro.

WITH users_groups AS (
    SELECT 
        user_id, 
        g.id,
        g.name AS group_name 
    FROM users u
    INNER JOIN user_actions ua ON ua.user_id = u.id
    LEFT JOIN groups g ON g.id = u.primary_group_id
    WHERE ua.action_type = 15
    GROUP BY user_id, g.id
),
    
tt_solution_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "total"
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
	
tt_solution_groups_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        ug.group_name,
    	COUNT(*) AS "tt_groups"
    FROM user_actions ua
    INNER JOIN users_groups ug ON ug.user_id = ua.user_id
    WHERE ua.action_type = 15
    GROUP BY ug.group_name, date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at), ug.group_name)
    
SELECT 
    ts.year,
    ts.month,
    COALESCE(tsg.group_name,'sem grupo'),
    tt_groups,
    total,
    TRUNC((tt_groups::decimal/total::decimal) *100,1) AS "%"
FROM tt_solution_groups_by_month tsg  
INNER JOIN tt_solution_by_month ts 
    ON ts.year = tsg.year AND ts.month = tsg.month 

Avise-me se este é o resultado esperado ou se devo ajustar algo.

Michelle

[Konrad_Sopala]

Sem problemas! Você é meu salvador aqui!

Quase perfeito! Não preciso das colunas tt_groups e total, apenas do número da porcentagem. Quanto à coluna group_name, como será uma consulta para um único grupo, também não há necessidade de incluí-la. No código da consulta, vou especificar apenas o primary_group_id, para que ele busque soluções apenas desse grupo específico.

[michebs (Michelle)]

Ajustado.

-- [params]
-- string :primary_group_id

WITH users_groups AS (
    SELECT 
        user_id, 
        g.id,
        g.name group_name 
    FROM users u
    INNER JOIN user_actions ua ON ua.user_id = u.id
    LEFT JOIN groups g ON g.id = u.primary_group_id
    WHERE ua.action_type = 15
    AND u.primary_group_id = :primary_group_id
    GROUP BY user_id, g.id
),
    
tt_solution_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "total"
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
	
tt_solution_groups_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        ug.group_name,
    	COUNT(*) AS "tt_groups"
    FROM user_actions ua
    INNER JOIN users_groups ug ON ug.user_id = ua.user_id
    WHERE ua.action_type = 15
    GROUP BY ug.group_name, date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at), ug.group_name)
    
SELECT 
    ts.year,
    ts.month,
    TRUNC((tt_groups::decimal/total::decimal) *100,1) AS "%"
FROM tt_solution_groups_by_month tsg  
INNER JOIN tt_solution_by_month ts 
    ON ts.year = tsg.year AND ts.month = tsg.month

[Konrad_Sopala]

Perfeito! Era isso que eu estava procurando!