오래된 할당 쿼리

커뮤니티 구축 데이터 및 보고
,
1

주제를 할당하기 시작하면, 필연적으로 방치된 할당이 생깁니다.

우리는 이를 찾기 위해 노력했습니다.

다른 분들에게 유용할 수 있으니, 여기 쿼리를 공유합니다.
커스터마이즈할 수 있는 2개의 서브쿼리가 있습니다:

  • SonarSourcers (왜 단순히 'staff’를 사용하지 않았는지 묻지 마세요)
  • teams
-- 목표: 방치된 할당 표시
--    방치란 할당 후 7일이 지났음에도
--    SonarSourcers의 공개 활동/일반 게시글이 없고
--    SonarSourcer의 게시글이 마지막 게시글이 아닌 경우를 의미합니다


WITH

-- 모든 할당된 주제 찾기
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
),

-- 각 할당된 주제에서 마지막 할당 찾기 (여러 개일 수 있음)
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
),

-- 회사에서 일하는 사용자 찾기
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'
),

-- 각 사용자의 주 팀 찾기
teams AS (
    SELECT distinct on (user_id) -- 일부 사용자는 2개의 그룹에 속합니다. (임의로) 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 -- 일부 중복 그룹 제거
                 g.id not in (10, 11, 12, 13, 14 -- 신뢰 수준 그룹
                    , 1, 2, 3 -- 내장 그룹
                    , 41 -- SonarSourcers
                    , 47 -- SonarCloud - 대신 스쿼드를 원함
                    , 53 -- .NET Scanner Guild
                    )
),

-- 할당된 주제에서 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 -- 일반
    GROUP BY p.topic_id, p.id,la.d
), 

-- 주제 내 마지막 공개 게시글 찾기
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 -- 일반
    GROUP BY p.topic_id
),

-- 마지막 게시글 목록에서 SonarSourcers의 게시글을 제거하여
-- 명확하게 사용자를 기다리고 있는 주제를 제외
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
),

-- 모두 통합
stale_topics AS (
    SELECT lsp.topic_id
        , max(lsp.last_assignment_date) as "할당 날짜"
        , max(lsp.last_staff_post) as "마지막 스태프 게시글"
        , 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
2

우리의 모델에서는 팀에 할당하고, 팀은 멤버(또는 서브팀)에 할당합니다.

초기 분류(triage)와 재할당 측면에서 팀들의 현황을 살펴보겠습니다:

-- 목표: 그룹으로의 할당 시점부터
--    서브팀 / SonarSourcer로의 재할당 시점까지의 시간을 찾는 것

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

WITH

-- 스레드 내에서 마지막 그룹 할당을 찾습니다
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
),

-- 할당 게시물의 세부 정보를 가져옵니다
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'

),

-- 그룹 할당 이후에 발생한 재할당을 찾습니다
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
),

-- 할당 게시물의 세부 정보를 가져옵니다
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'
),

-- 각 스레드의 재할당까지 소요된 일수를 계산합니다
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

참고로 이 쿼리는 커스터마이징할 부분이 없으며, 모든 사람에게 "그냥 작동"하도록 되어 있습니다.