# Stale assignments query

**URL:** <https://meta.discourse.org/t/stale-assignments-query/243421>\
**Category:** Data & reporting\
**Tags:** assign, sql-query\
**Created:** [October 31, 2022, 7:23pm UTC](https://meta.discourse.org/t/stale-assignments-query/243421 "2022-10-31T19:23:25Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![ganncamp](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/ganncamp/32/106199_2.png) [@ganncamp](https://meta.discourse.org/u/ganncamp)\
**Post date:** [November 4, 2022, 5:56pm UTC](https://meta.discourse.org/t/stale-assignments-query/243421/2 "2022-11-04T17:56:35Z")

</div>

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:

```plaintext
-- 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.

---

_[View the full topic](https://meta.discourse.org/t/stale-assignments-query/243421)._