Quiero mostrar tanto al usuario (cuando esté disponible) como al grupo. Y me gusta mucho y aprecio el HMTL de ninja que me da un nombre bien formateado y enlazado a partir de un id.
Así que construir eso manualmente no vale la pena para mí, especialmente porque tendría que unir tablas adicionales para que funcione.
Pero aquí está el informe en cuestión:
-- objetivo: mostrar asignaciones obsoletas,
-- donde obsoleto es 7 días desde la asignación
-- VER TAMBIÉN: Asignaciones obsoletas para un grupo
WITH
-- encontrar los usuarios que trabajan para la empresa
SonarSourcers AS (
SELECT u.id AS user_id
FROM groups g
INNER JOIN group_users gu ON g.id=gu.group_id
INNER JOIN users u ON u.id = gu.user_id
WHERE g.name='sonarsourcers'
),
-- encontrar grupos de interés
teams AS (
SELECT id as group_id
FROM groups g
WHERE g.id not in (10, 11, 12, 13, 14 -- grupos de nivel de confianza
, 1, 2, 3 -- grupos integrados
, 41 -- SonarSourcers
, 47 -- SonarCloud - queremos los escuadrones en su lugar
, 53 -- .NET Scanner Guild
, 0 -- "todos"...?
, 65, 66, 67 -- subgrupos de CFam
)
),
-- encontrar el equipo principal de cada SonarSourcer
user_team AS (
SELECT distinct on (user_id) -- algunos usuarios tienen 2 grupos. reducir (arbitrariamente) a 1
ss.user_id, t.group_id
FROM SonarSourcers ss
JOIN group_users gu on gu.user_id=ss.user_id
JOIN teams t on t.group_id = gu.group_id
),
-- encontrar temas asignados
-- que no están ni cerrados ni resueltos
-- que fueron asignados hace más de 7 días
assigned_topics AS (
SELECT a.topic_id
, a.updated_at
, CASE WHEN assigned_to_type = 'User' THEN assigned_to_id END AS user_id
, CASE WHEN assigned_to_type = 'Group' THEN assigned_to_id ELSE user_team.group_id END AS group_id
FROM assignments a
JOIN topics t ON a.topic_id = t.id
LEFT JOIN user_team ON user_team.user_id=assigned_to_id
-- eliminar temas cerrados - parte 1
JOIN posts p on p.topic_id = t.id
LEFT JOIN post_custom_fields pcf ON pcf.post_id=p.id AND pcf.name='is_accepted_answer'
WHERE active = true
AND a.updated_at < current_date - INTERVAL '7'
AND t.closed = false -- eliminar temas cerrados - parte 2
AND pcf.id IS NULL -- eliminar temas resueltos
GROUP BY a.topic_id, a.updated_at, assigned_to_type, assigned_to_id, user_team.group_id, t.updated_at
ORDER BY t.updated_at asc
),
-- encontrar la última publicación pública en cada tema asignado
-- usaremos esto para filtrar los temas donde el último publicador fue un miembro del grupo
last_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
WHERE post_type = 1 -- regular
GROUP BY p.topic_id
),
-- encontrar la última publicación pública en cada tema del grupo asignado
-- usaremos esto para filtrar los que realmente están siendo respondidos
last_group_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
, max(created_at) as last_public_post
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
JOIN user_team on user_team.user_id=p.user_id
WHERE post_type = 1 -- regular
GROUP BY p.topic_id
),
stale AS (
SELECT COUNT(lp.topic_id) AS stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- no SonarSourcers
OR user_team.group_id != at.group_id)
AND lgp.last_public_post <= current_date - INTERVAL '7' -- última publicación hace 7+ días
AND lgp.last_public_post > current_date - INTERVAL '14' -- última publicación hace <14 días
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
super_stale AS (
SELECT COUNT(lp.topic_id) as super_stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- no SonarSourcers
OR user_team.group_id != at.group_id)
AND (lgp.last_public_post <= current_date - INTERVAL '14'
OR lgp.last_public_post IS NULL)
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
aggregated AS (
SELECT COALESCE(s.user_id,ss.user_id) AS user_id
, COALESCE(s.group_id,ss.group_id) AS group_id
, COALESCE(stale,0) AS stale
, COALESCE(super_stale,0) AS super_stale
FROM stale s
FULL JOIN super_stale ss using (user_id)
)
-- En este punto, todavía hay dos filas para algunos equipos (filas con user_id nulo).
-- Usar agrupación y agregación para colapsar filas y eliminar nulos
-- Además, volver a añadir los equipos 'faltantes'
SELECT user_id
, group_id
, COALESCE(MAX(stale),0) AS "Obsoleto (7d)"
, COALESCE(MAX(super_stale),0) AS "Super obsoleto (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super obsoleto (14d)" desc, "Obsoleto (7d)" desc
