# 识别新兴的顶级贡献者

**URL:** <https://meta.discourse.org/t/identifying-up-and-coming-top-contributors/317633>\
**Category:** Community Building\
**Created:** [2024年七月22日 16:40 UTC](https://meta.discourse.org/t/identifying-up-and-coming-top-contributors/317633 "2024-07-22T16:40:32Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![ganncamp](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/ganncamp/32/106199_2.png) [@ganncamp](https://meta.discourse.org/u/ganncamp)\
**Post date:** [2024年七月22日 19:17 UTC](https://meta.discourse.org/t/identifying-up-and-coming-top-contributors/317633/4 "2024-07-22T19:17:27Z")

</div>

我们第一次尝试的是排行榜，但它似乎更看重纯粹的数量（想想“修好了吗？修好了吗？修好了吗？你好？”）而不是质量。好吧，是的，“质量”很难量化，尽管可以进行一些[明显的调整](https://meta.discourse.org/t/throttle-back-leaderboard-cheers-for-posts-on-own-topics/282822)。

我们开始通过以下方式识别真正有帮助的用户：

> **寻找另一位用户是第一个回答的帖子**
>
> ```sql
> -- 目标：查找第一个非 OP 回复是非 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'
> ),
> 
> -- 查找由普通用户创建的“常规”主题
> 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 -- 排除 SonarSourcers 发起的主题
> AND tt.topic_id IS NULL -- 排除标记为 me-too 的主题
> AND user_id > 0 -- 排除 DiscoBot 的“欢迎”PM/教程主题
> AND visible = TRUE
> AND archived = FALSE
> AND archetype='regular'
> AND created_at::DATE > '2023-07-01'
> ),
> 
> -- 查找用户主题的第一个非 OP 回复
> 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 -- 排除 SonarSourcer 是第一个回复的主题
> AND ub.user_id IS NULL -- 排除已授予徽章的主题
> ORDER BY mr.created_at DESC
> 
> ```

一旦我们确定了这些帖子，我们就会评估用户的回复，然后要么授予回复用户“用户互助”徽章，要么在帖子中添加一个（非员工不可见）的“me-too”标签。 （顺便说一句，这个用户回复报告还有助于快速发现我们云服务中的事件。 😂）

从那里开始，就可以[进一步奖励](https://meta.discourse.org/t/a-silver-badge-for-each-5-bronze-badges/283284)有帮助的用户，并识别他们以进行进一步培养。

---

_[View the full topic](https://meta.discourse.org/t/identifying-up-and-coming-top-contributors/317633)._