Identifying up-and-coming top contributors

community
4

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. :joy:)

From there it gets easy to further reward helpful users, and identify them for further nurtures.

5 Likes