Our first run at this was the leaderboard, but it seems to weight pure volume (think “Is it fixed yet? Is it fixed yet? Is it fixed yet? Hello?”) versus quality. Okay, yes “quality” is hard to algo, altho there are some obvious tweaks to be made.
What we’ve started doing to identify truly helpful users is to
look for threads where another user is the first to answer
-- Goal: Find topics where first non-OP reply is a non-SonarSourcer
WITH
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'
),
tagged_topics AS (
SELECT tt.topic_id
FROM topic_tags tt
JOIN tags t on t.id=tt.tag_id
WHERE name = 'me-too'
),
-- find 'regular' topics created by normal users
topic_user AS (
SELECT id as topic_id, user_id, created_at
FROM topics
LEFT JOIN SonarSourcers ss USING(user_id)
LEFT JOIN tagged_topics tt on topics.id = tt.topic_id
WHERE ss.user_id IS NULL -- omit topics started by SonarSourcers
AND tt.topic_id IS NULL -- omit topics tagged with me-too
AND user_id > 0 -- omit DiscoBot's 'Welcome' PM/tutorial threads
AND visible = TRUE
AND archived = FALSE
AND archetype='regular'
AND created_at::DATE > '2023-07-01'
),
-- find first non-OP reply to user topics
min_response AS (
SELECT p.topic_id, tu.created_at, MIN(post_number) as post_number
FROM posts p
JOIN topic_user tu USING(topic_id)
WHERE p.post_type = 1
AND p.user_id != tu.user_id
AND p.post_number > 1
AND p.hidden = false
AND p.deleted_at IS NULL
GROUP BY topic_id, tu.created_at
)
SELECT p.topic_id, p.user_id, mr.created_at::DATE as thread_date
FROM posts p
JOIN min_response mr ON p.topic_id = mr.topic_id AND p.post_number=mr.post_number
LEFT JOIN SonarSourcers ss ON p.user_id=ss.user_id
LEFT JOIN user_badges ub on p.id = ub.post_id and ub.badge_id=110
WHERE ss.user_id IS NULL -- eliminate topics where SonarSourcer is first to respond
AND ub.user_id IS NULL -- eliminate topics where a badge has already been granted
ORDER BY mr.created_at DESC
Once we identify those threads, we evaluate the user response and then either award the responding user a “Users helping Users” badge or tag the thread with a (invisible to non-staff) “me-too” tag. (BTW, this user-response report has the happy side effect of spotting incidents in our cloud service quickly. )
From there it gets easy to further reward helpful users, and identify them for further nurtures.