TL2 差距 - 如此之小，却又如此之大

社区
1

我最近一直在关注信任等级差距报告。特别是 TL2，以及是什么阻碍了用户从 TL1 晋升到 TL2。

查看用户的差距，让我意识到这在多大程度上是关于社区的。这不仅仅是关于我的问题和我的观点。它也关乎与其他用户的互动。这很好。

然而，我发现，意识到有多少用户非常接近 TL2，却“仅仅”缺少……“回馈”，这有点令人沮丧。

我想知道是否有人（也许是两岁孩子的父母？ :joy: ）对如何鼓励人们培养社区精神和考虑/帮助他人有什么想法……？

供参考：

Selection_056
Selection_056720×551 22.7 KB

Data Explorer Query

对于想要查询的人：

with

-- 信任等级 1 用户
tl AS (
    SELECT id as user_id, trust_level, last_seen_at
    FROM users
    WHERE trust_level = 1
),

-- 近 3 个月内可见的用户 + 访问次数、阅读帖子数、阅读时间
pr AS (
    SELECT user_id,
        count(1) as visits,
        sum(posts_read) as posts_read,
        SUM(time_read)/60 as minutes_reading_time,
        DATE(last_seen_at) AS last_seen
    FROM user_visits
    INNER JOIN tl using (user_id)
    WHERE DATE(last_seen_at) >= CURRENT_DATE - INTERVAL '3 month'
    GROUP BY user_id, last_seen
    ORDER BY visits, last_seen DESC
),

-- 回复的主题
trt as (
    select posts.user_id,
           count(distinct topic_id) as replied_count
    from posts
    INNER JOIN tl using (user_id)
    INNER JOIN topics ON topics.id = posts.topic_id
    WHERE topics.user_id <> posts.user_id
        AND posts.deleted_at IS NULL AND topics.deleted_at IS NULL
--        AND topics.archetype <> 'private_message'
        AND archetype = 'regular'
    GROUP BY posts.user_id
    ORDER BY replied_count DESC
),

-- 所有时间查看的主题
tvat as (
    select tv.user_id,
        COUNT(distinct tv.topic_id) AS topic_id
    FROM topic_views tv
    LEFT JOIN topics t on tv.topic_id=t.id
    INNER JOIN tl on tv.user_id=tl.user_id
    WHERE
        t.archetype = 'regular'
        AND t.deleted_at is null
    group by tv.user_id
),

likes AS (
    SELECT user_id,
        likes_given, likes_received
    from user_stats
    INNER JOIN tl using (user_id)
)

SELECT  pr.user_id,
        pr.last_seen as "最后登录时间",
        -- 访问天数：15
        greatest(15-coalesce(pr.visits,0),0) as "访问天数差距",
        -- 回复主题数：3
        greatest(3-coalesce(trt.replied_count,0), 0)  as "回复主题差距",
        -- 进入的主题数：20
        greatest(20-coalesce(tvat.topic_id,0),0) as "查看主题差距",
        -- 阅读帖子数：100
        greatest(100-coalesce(pr.posts_read,0),0) as "阅读帖子差距",
        -- 阅读帖子时间：60分钟
        greatest(60-pr.minutes_reading_time,0) as "阅读时间差距",
        -- 点赞数：1
        greatest(1-likes.likes_given,0) as "点赞差距",
        -- 被点赞数：1
        greatest(1-likes.likes_received,0) as "被点赞差距"

FROM pr
left join trt using (user_id)
left join tvat using (user_id)
LEFT JOIN likes using (user_id)


ORDER BY
  pr.visits DESC

LIMIT 500
3 个赞
2

这真是一个黄金问题！我想这归结于你的社区文化以及其中发生的活动。

如果你能增加每个成员之间的联系或纽带，他们将更愿意回馈。但这只在你所在的社区需要这种联系时才有效。有些社区不需要成员之间的联系，当你试图建立这种联系时，社区反而会破裂。

3 个赞