# 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:** 1

<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:** [October 31, 2022, 7:23pm UTC](https://meta.discourse.org/t/stale-assignments-query/243421/1 "2022-10-31T19:23:25Z")

</div>

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

```

---

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