Consulta de Data Explorer sobre el % de temas resueltos por un grupo específico de usuarios

[Konrad_Sopala]

¡Hola a todos los que están usando el Explorador de Datos!

(Perdónenme, @michebs - solo tendré dos preguntas más, espero que otras personas también se beneficien)

¿Alguien ha creado alguna vez una consulta en el Explorador de Datos para el % de temas resueltos por un grupo específico, agrupados por mes y año?

El resultado sería algo así, pero la última columna mostraría el porcentaje:

[sam (Sam Saffron)]

Podemos manejar algo así sin problema, todos los datos existen.

“% resuelto”… ¿a qué te refieres exactamente.

• El post se publicó en enero.
• El grupo awesome obtuvo una respuesta aceptada en febrero.
• 73 temas más obtuvieron soluciones en febrero por parte de otros grupos y usuarios.

Así que supongo que el “grupo awesome” tiene una tasa de resolución del 1,35 % para febrero?

[Konrad_Sopala]

Digamos que hay 100 soluciones en un mes. Hay veinte personas que pertenecen a algún grupo y los usuarios comenzaron a marcar sus respuestas como soluciones, un total de veinte de ellas. Quiero una consulta donde pueda especificar su ID de grupo principal en el script y obtener los datos por mes para mostrar que, para este mes, es 20/100, es decir, un 20%.

[michebs (Michelle)]

Hola Konrad, disculpa la demora.
Sigo validando algunas consultas, pero aprovecho para confirmar: ¿este es el resultado que estás esperando?

year	month	group_name	tt_groups	total	%
2021	1	team1	40	70	57
2021	1	team2	30	70	43

[michebs (Michelle)]

He intentado hacer las consultas más detalladas para facilitar su comprensión y mantenimiento en el 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,'sin 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 

Avísame si este es el resultado que esperabas o si debo ajustar algo.

Michelle

[Konrad_Sopala]

¡No te preocupes! ¡Eres mi salvador aquí!

¡Casi perfecto! No necesito las columnas tt_groups ni total, solo el número del porcentaje. En cuanto a la columna group_name, será una consulta para un solo grupo, así que tampoco hace falta esa columna. Dentro del código de la consulta, simplemente especificaré el primary_group_id, de modo que buscará solo las soluciones de este grupo exacto.

[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]

¡Perfecto! ¡Eso es lo que estaba buscando!