Our workflow is to assign to groups & then (typically) the groups assign to individuals, and I’m working on reporting around that.
I’m ending up with things like this:
Ideally I’d be able to COALESCE those NULL user values to ''. Except that’s a string, and what was selected was an integer (user_id), so I get an error. If I COALESCE it to 0, it looks equally bad, just in a different way.
Has anyone mastered this?
This is a pretty tricky query, I think there may be some ninja HTML feature inside data explorer, so you would basically use something like:
case when group_id IS NOT NULL
then 'http://abc/groups/` || group_id::text
else 'http://abc/user/` || user_id::text
end
And then construct a link from that. If you want to just show the name of the group / user in one column without it being a hyperlink it is a bit easier.
What is the SQL you have now?
I want to show both user (when available) and group. And I really like and appreciate the ninja HMTL that gives me a nicely formatted and linked name from an id.
So building that manually just isn’t worth it for me, particularly since I’d have to join extra tables back in to make it work.
But here’s the report in question:
-- goal: show stale assignments,
-- where stale is 7 days since assignment
-- SEE ALSO: Stale assignments for a group
WITH
-- 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 groups of interest
teams AS (
SELECT id as group_id
FROM groups g
WHERE 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
, 0 -- "everyone"...?
, 65, 66, 67 -- CFam sub-groups
)
),
-- find each SonarSourcer's primary team
user_team AS (
SELECT distinct on (user_id) -- some users have 2 groups. narrow (arbitrarily) to 1
ss.user_id, t.group_id
FROM SonarSourcers ss
JOIN group_users gu on gu.user_id=ss.user_id
JOIN teams t on t.group_id = gu.group_id
),
-- find assigned topics
-- that are neither closed nor solved
-- that were assigned more than 7 days ago
assigned_topics AS (
SELECT a.topic_id
, a.updated_at
, CASE WHEN assigned_to_type = 'User' THEN assigned_to_id END AS user_id
, CASE WHEN assigned_to_type = 'Group' THEN assigned_to_id ELSE user_team.group_id END AS group_id
FROM assignments a
JOIN topics t ON a.topic_id = t.id
LEFT JOIN user_team ON user_team.user_id=assigned_to_id
-- eliminate closed topics - part 1
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 -- eliminate closed topics - part 2
AND pcf.id IS NULL -- eliminate solved topics
GROUP BY a.topic_id, a.updated_at, assigned_to_type, assigned_to_id, user_team.group_id, t.updated_at
ORDER BY t.updated_at asc
),
-- find the last public post in each assigned topic
-- we'll use this to filter out topics were a group member was last poster
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
WHERE post_type = 1 -- regular
GROUP BY p.topic_id
),
-- find the last public post on each topic from the assigned group
-- we'll use this to filter out the ones actually being responded to
last_group_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
, max(created_at) as last_public_post
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
JOIN user_team on user_team.user_id=p.user_id
WHERE post_type = 1 -- regular
GROUP BY p.topic_id
),
stale AS (
SELECT COUNT(lp.topic_id) AS stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- non-SonarSourcers
OR user_team.group_id != at.group_id)
AND lgp.last_public_post <= current_date - 7 -- last post 7+ days before today
AND lgp.last_public_post > current_date - 14 -- last post <14 days before today
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
super_stale AS (
SELECT COUNT(lp.topic_id) as super_stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- non-SonarSourcers
OR user_team.group_id != at.group_id)
AND (lgp.last_public_post <= current_date - 14
OR lgp.last_public_post IS NULL)
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
aggregated AS (
SELECT COALESCE(s.user_id,ss.user_id) AS user_id
, COALESCE(s.group_id,ss.group_id) AS group_id
, COALESCE(stale,0) AS stale
, COALESCE(super_stale,0) AS super_stale
FROM stale s
FULL JOIN super_stale ss using (user_id)
)
-- At this point, there are still two rows for some teams (rows w/null user_id).
-- Use grouping & aggregation to collapse rows & eliminate nulls
-- Also, add back in 'missing' teams
SELECT user_id
, group_id
, COALESCE(MAX(stale),0) AS "Stale (7d)"
, COALESCE(MAX(super_stale),0) AS "Super stale (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super stale (14d)" desc, "Stale (7d)" desc