Stale assignments query

Once you start assigning topics, inevitably you end up with stale assignments.

We set out to find them.

So here’s the query in case it’s useful to others.
There are 2 subqueries to customize:

  • SonarSourcers (don’t ask me why we didn’t just use ‘staff’)
  • teams
-- goal: show stale assignments,
--    where stale is 7 days since assignment with 
--    no public activity / 'regular' posts from SonarSourcers
--    and a SonarSourcer's post is not the last post


WITH

-- find all assigned topics
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
),

-- on each assigned topic, find the LAST assignment (there may be multiple)
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
),

-- find the users who work for the company
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'
),

-- find each person's primary team
teams AS (
    SELECT distinct on (user_id) -- some users have 2 groups. narrow (arbitrarily) to 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 -- eliminate some duplicate groups
                 g.id not in (10, 11, 12, 13, 14 -- trust level groups
                    , 1, 2, 3 -- built-in groups
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud - we want the squads instead
                    , 53 -- .NET Scanner Guild
                    )
),

-- find the last post in an assigned topic that's from a 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
), 

-- find the very last public post in the topic
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
),

-- eliminate SonarSourcers' posts from the last post list to eliminate topics 
-- where we're clearly waiting on the user
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
),

-- put it all together
stale_topics AS (
    SELECT lsp.topic_id
        , max(lsp.last_assignment_date) as "Assignment date"
        , max(lsp.last_staff_post) as "Last Staff Post"
        , 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 Likes

In our model, we assign to teams & teams assign to members (or sub-teams).

Let’s see how the teams are doing in terms of initial triage and reassignment:

-- goal: find time from assignment to group 
--    to re-assignment to sub-team / SonarSourcer

-- [params]
-- date :start_date = 2022-10-01

WITH

-- find last group assignment in the 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
),

-- get the details of the assignment post
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'

),

-- find the re-assignment that came after the group assignment
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
),

-- get the details of the assignment post
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'
),

-- calculate days to reassignment for each 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

Note that there’s nothing to customize in this one; it should “just work” for everyone.

2 Likes