# 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:** 2
**Page:** 1
### Author:  [@ganncamp](https://meta.discourse.org/u/ganncamp)
#### Post date: [October 31, 2022, 7:23pm UTC](https://meta.discourse.org/t/stale-assignments-query/243421/1 "2022-10-31T19:23:25Z")
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`
```plaintext
-- 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
```
---
### Author:  [@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")
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.