Abgelaufene Zuweisungsabfrage

Daten & Berichterstattung
,
1

Sobald Sie anfangen, Themen zuzuweisen, landen Sie unweigerlich bei veralteten Zuweisungen.

Wir haben uns auf die Suche nach ihnen gemacht.

Hier ist also die Abfrage, falls sie für andere nützlich ist.
Es gibt 2 Unterabfragen, die angepasst werden können:

  • SonarSourcers (fragen Sie mich nicht, warum wir nicht einfach ‘Mitarbeiter’ verwendet haben)
  • teams
-- Ziel: veraltete Zuweisungen anzeigen,
--    wobei veraltet 7 Tage seit der Zuweisung ohne öffentliche Aktivität / 'reguläre' Beiträge von SonarSourcers bedeutet
--    und ein Beitrag eines SonarSourcers nicht der letzte Beitrag ist


WITH

-- Alle zugewiesenen Themen finden
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
),

-- Für jedes zugewiesene Thema die LETZTE Zuweisung finden (es kann mehrere geben)
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
),

-- Die Mitarbeiter finden, die für das Unternehmen arbeiten
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'
),

-- Die Hauptgruppe jedes Mitarbeiters finden
teams AS (
    SELECT distinct on (user_id) -- einige Benutzer haben 2 Gruppen. Eingrenzen (willkürlich) auf 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 -- einige doppelte Gruppen eliminieren
                 g.id not in (10, 11, 12, 13, 14 -- Vertrauensstufengruppen
                    , 1, 2, 3 -- eingebaute Gruppen
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud - wir wollen stattdessen die Squads
                    , 53 -- .NET Scanner Guild
                    )
),

-- Den letzten Beitrag in einem zugewiesenen Thema finden, der von einem SonarSourcer stammt
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 -- regulär
    GROUP BY p.topic_id, p.id,la.d
),

-- Den allerletzten öffentlichen Beitrag im Thema finden
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 -- regulär
    GROUP BY p.topic_id
),

-- Die Beiträge von SonarSourcers aus der Liste der letzten Beiträge eliminieren, um Themen zu eliminieren,
-- bei denen wir eindeutig auf den Benutzer warten
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
),

-- Alles zusammenfügen
stale_topics AS (
    SELECT lsp.topic_id
        , max(lsp.last_assignment_date) as "Zuweisungsdatum"
        , max(lsp.last_staff_post) as "Letzter Mitarbeitertrag"
        , 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 „Gefällt mir“
2

In unserem Modell weisen wir Teams zu und Teams weisen Mitglieder (oder Unterteams) zu.

Sehen wir uns an, wie die Teams in Bezug auf die anfängliche Triage und Neuzuweisung abschneiden:

-- Ziel: Zeit von der Zuweisung zur Gruppe bis zur Neuzuweisung an ein Unterteam / SonarSourcer finden
--    bis zur Neuzuweisung an ein Unterteam / SonarSourcer
-- [params]
-- date :start_date = 2022-10-01

WITH

-- Finde die letzte Gruppenzuweisung im Thread
group_assignment AS (
    SELECT max(p.post_number) AS assignment_post, 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 >= :start_date
    GROUP BY p.topic_id
),

-- Hole die Details des Zuweisungsposts
group_assignment_deets AS (
    SELECT p.id as post_id, p.created_at as d, p.topic_id, pcf.value as who
    FROM posts p
    JOIN group_assignment ga on ga.topic_id=p.topic_id
        AND p.post_number = ga.assignment_post
    JOIN post_custom_fields pcf ON pcf.post_id = p.id
    WHERE pcf.name = 'action_code_who'

),

-- Finde die Neuzuweisung, die nach der Gruppenzuweisung erfolgte
next_assignment AS (
    SELECT min(p.post_number) AS assignment_post, p.topic_id --, min(p.created_at) as d
    FROM posts p
    JOIN group_assignment 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.assignment_post
    GROUP BY p.topic_id
),

-- Hole die Details des Zuweisungsposts
next_assignment_deets AS (
    SELECT p.id as post_id, p.created_at as d, p.topic_id, pcf.value as who
    FROM posts p
    JOIN next_assignment na ON na.topic_id=p.topic_id
        AND p.post_number = na.assignment_post
    JOIN post_custom_fields pcf ON pcf.post_id = p.id
    WHERE pcf.name = 'action_code_who'
),

-- Berechne die Tage bis zur Neuzuweisung für jeden Thread
days_per_team AS (
    SELECT gad.who as team
        , extract(epoch from (nad.d - gad.d)/86400) as days
    FROM group_assignment_deets gad
    JOIN next_assignment_deets nad using(topic_id)
)

SELECT
    team as "Team"
    , count(*) as "Thread count"
    , round(avg(days)::numeric,2) as "Avg days to reassignment"
    , round(max(days)::numeric,2) as "Max"
    , round(min(days)::numeric,6) as "Min"
FROM days_per_team
GROUP BY team
ORDER BY "Thread count" desc

Beachte, dass es hier nichts anzupassen gibt; es sollte für jeden funktionieren.

2 „Gefällt mir“