Consulta de asignaciones obsoletas

Datos e informes
,
1

Una vez que empiezas a asignar temas, inevitablemente terminas con asignaciones obsoletas.

Nos propusimos encontrarlas.

Así que aquí tienes la consulta en caso de que sea útil para otros.
Hay 2 subconsultas para personalizar:

  • SonarSourcers (no me preguntes por qué no usamos simplemente ‘staff’)
  • teams
-- objetivo: mostrar asignaciones obsoletas,
--    donde obsoleto es 7 días desde la asignación con
--    sin actividad pública / publicaciones 'regulares' de SonarSourcers
--    y una publicación de SonarSourcer no es la última publicación


WITH

-- encontrar todos los temas asignados
assigned_topics AS (
    SELECT a.topic_id
        , assigned_to_type
        , assigned_to_id
    FROM assignments a
    JOIN topics t ON a.topic_id = t.id
    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 - INTEGER '7'
        AND t.closed = false
        AND pcf.id IS NULL
--        AND a.updated_at > '2022-01-01'
    ORDER BY t.updated_at desc
),

-- en cada tema asignado, encontrar la ÚLTIMA asignación (puede haber varias)
last_assignment AS (
    SELECT max(p.post_number) AS assignment_post, p.topic_id, max(p.created_at) as d
    FROM posts p
    JOIN assigned_topics ON p.topic_id=assigned_topics.topic_id
    WHERE p.action_code in ('assigned', 'assigned_group', 'assigned_group_to_post', 'assigned_to_post')
    GROUP BY p.topic_id, p.created_at
),

-- encontrar a los usuarios que trabajan en 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 el equipo principal de cada persona
teams AS (
    SELECT distinct on (user_id) -- algunos usuarios tienen 2 grupos. reducir (arbitrariamente) a 1
        ss.user_id, g.id as group_id
    FROM SonarSourcers ss
    JOIN group_users gu on gu.user_id=ss.user_id
    JOIN groups g on g.id = gu.group_id
    WHERE -- eliminar algunos grupos duplicados
                 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
                    )
),

-- encontrar la última publicación en un tema asignado que sea de un SonarSourcer
last_staff_post AS (
    SELECT p.id AS post
        , p.topic_id
        , max(p.created_at) AS last_staff_post
        , la.d AS last_assignment_date
    FROM posts p
    JOIN last_assignment la ON p.topic_id=la.topic_id
    JOIN SonarSourcers ss ON ss.user_id=p.user_id
    WHERE post_type = 1 -- regular
    GROUP BY p.topic_id, p.id,la.d
),

-- encontrar la última publicación pública en el tema
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
    JOIN users u ON p.user_id=u.id
    WHERE post_type = 1 -- regular
    GROUP BY p.topic_id
),

-- eliminar las publicaciones de SonarSourcers de la lista de últimas publicaciones para eliminar temas
-- donde claramente estamos esperando al usuario
last_post_trust_level_limit AS (
    SELECT lp.topic_id
    FROM users u
    JOIN posts p ON u.id=p.user_id
    JOIN last_post lp ON p.id = lp.post_id
    WHERE u.trust_level < 4
),

-- juntarlo todo
stale_topics AS (
    SELECT lsp.topic_id
        , max(lsp.last_assignment_date) as "Fecha de asignación"
        , max(lsp.last_staff_post) as "Última publicación del personal"
        , CASE WHEN at.assigned_to_type = 'User'  THEN u.id END AS user_id
        , CASE WHEN at.assigned_to_type = 'Group' THEN g.id ELSE teams.group_id END AS group_id
    FROM last_staff_post lsp
    JOIN assigned_topics at ON lsp.topic_id=at.topic_id
    JOIN last_post_trust_level_limit lptll ON lsp.topic_id = lptll.topic_id
    FULL OUTER JOIN users u ON assigned_to_id=u.id
    FULL OUTER JOIN teams on teams.user_id=u.id
    FULL OUTER JOIN groups g ON assigned_to_id=g.id
    WHERE lsp.last_staff_post <= lsp.last_assignment_date + interval '7 days'
    GROUP BY at.assigned_to_id, lsp.topic_id, at.assigned_to_type, u.id, g.id, teams.group_id
)

SELECT count(topic_id), user_id, group_id
FROM stale_topics
GROUP BY user_id, group_id
ORDER BY group_id
3 Me gusta
2

En nuestro modelo, asignamos a los equipos y los equipos se asignan a los miembros (o sub-equipos).

Veamos cómo están funcionando los equipos en términos de triaje inicial y reasignación:

-- objetivo: encontrar el tiempo desde la asignación al grupo
--    hasta la reasignación a sub-equipo / SonarSourcer

-- [parámetros]
-- fecha :fecha_inicio = 2022-10-01

CON

-- encontrar la última asignación de grupo en el hilo
grupo_asignacion AS (
    SELECT max(p.post_number) AS post_asignacion, p.topic_id --, max(p.created_at) as d
    FROM posts p
    WHERE p.action_code in ('assigned_group', 'assigned_group_to_post')
        AND p.created_at >= :fecha_inicio
    GROUP BY p.topic_id
),

-- obtener los detalles de la publicación de asignación
grupo_asignacion_detalles AS (
    SELECT p.id as post_id, p.created_at as d, p.topic_id, pcf.value as quien
    FROM posts p
    JOIN grupo_asignacion ga on ga.topic_id=p.topic_id
        AND p.post_number = ga.post_asignacion
    JOIN post_custom_fields pcf ON pcf.post_id = p.id
    WHERE pcf.name = 'action_code_who'

),

-- encontrar la reasignación que vino después de la asignación de grupo
siguiente_asignacion AS (
    SELECT min(p.post_number) AS post_asignacion, p.topic_id --, min(p.created_at) as d
    FROM posts p
    JOIN grupo_asignacion ga on ga.topic_id=p.topic_id
    WHERE p.action_code in ('assigned', 'assigned_to_post', 'reassigned_group', 'reassigned')
        AND p.post_number > ga.post_asignacion
    GROUP BY p.topic_id
),

-- obtener los detalles de la publicación de asignación
siguiente_asignacion_detalles AS (
    SELECT p.id as post_id, p.created_at as d, p.topic_id, pcf.value as quien
    FROM posts p
    JOIN siguiente_asignacion na ON na.topic_id=p.topic_id
        AND p.post_number = na.post_asignacion
    JOIN post_custom_fields pcf ON pcf.post_id = p.id
    WHERE pcf.name = 'action_code_who'
),

-- calcular días hasta la reasignación para cada hilo
dias_por_equipo AS (
    SELECT gad.quien as equipo
        , extract(epoch from (nad.d - gad.d)/86400) as dias
    FROM grupo_asignacion_detalles gad
    JOIN siguiente_asignacion_detalles nad using(topic_id)
)

SELECT
    equipo as "Equipo"
    , count(*) as "Número de hilos"
    , round(avg(dias)::numeric,2) as "Días promedio hasta reasignación"
    , round(max(dias)::numeric,2) as "Máximo"
    , round(min(dias)::numeric,6) as "Mínimo"
FROM dias_por_equipo
GROUP BY equipo
ORDER BY "Número de hilos" desc

Tenga en cuenta que no hay nada que personalizar en este; debería “simplemente funcionar” para todos.

2 Me gusta