Searching for historical assignments

Community Building Data & reporting
6

Wow @michebs – thanks for your help!

We had started to cobble a similar query together by the end of today (finding the post_custom_fields table blew our minds), but yours is much cleaner in the end.

We did notice there were some missing entries when topics had been directly reassigned rather than unassigned and then assigned, so I changed a little bit the WHERE condition to account for this. So finally we landed on:

SELECT 
    pcf.value AS group,
    p.topic_id,
    p.user_id AS "assigned_by_user_id",
    pcf.created_at::date
FROM posts p
INNER JOIN post_custom_fields pcf ON pcf.post_id = p.id
WHERE (action_code = 'assigned_group' OR action_code = 'reassigned_group') 
    AND pcf.name = 'action_code_who'
ORDER BY pcf.value, pcf.created_at DESC

Thanks again for your help. :heart: