TL2 gap - so small yet so large

I’ve been looking at trust level gap reporting lately. Specifically TL2 & what’s keeping users from being promoted from TL1 to TL2.

Looking at usrs’ gaps has made me realize how much of this is about community. It’s not just about my questions and my opinions. It’s about engaging with other users too. And that’s great.

And yet I find it kinda depressing to realize how many users are so close to TL2, and are “just” missing… “giving back”.

I’m wondering if anyone (parents of 2-year-olds, perhaps? :joy: ) has any thoughts about how to encourage folks to a spirit of community & thinking of / helping others…?

For reference:

Data Explorer Query

For those who want the query:


-- Trust Level 1 users
tl AS (
    SELECT id as user_id, trust_level, last_seen_at
    FROM users
    WHERE trust_level = 1

-- Users seen in last 3mo + visits, posts read, reading time
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

-- Topics replied to
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 = 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

-- Topics Viewed All Time
tvat as (
    select tv.user_id,
        COUNT(distinct tv.topic_id) AS topic_id
    FROM topic_views tv
    LEFT JOIN topics t on
    INNER JOIN tl on tv.user_id=tl.user_id
        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 "Last seen",
        -- days visited: 15
        greatest(15-coalesce(pr.visits,0),0) as "Days visited gap",
        -- topic replies: 3
        greatest(3-coalesce(trt.replied_count,0), 0)  as "Topic reply gap",
        -- topics entered: 20
        greatest(20-coalesce(tvat.topic_id,0),0) as "Topic viewed gap",
        -- posts read: 100
        greatest(100-coalesce(pr.posts_read,0),0) as "Posts Read gap",
        -- time spent reading posts: 60min
        greatest(60-pr.minutes_reading_time,0) as "Reading time gap",
        -- likes given: 1
        greatest(1-likes.likes_given,0) as "Likes given gap",
        -- likes received: 1
        greatest(1-likes.likes_received,0) as "Likes received gap"

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

  pr.visits DESC

This is such a golden question! I guess it boils down to your community culture as well as the activities happening within it.

If you can increase the bonds or connection between each member, they would more willing to give back. But this would work if your community is one that requires this. There are communities that do not need connections between members and when you try to build that, the community breaks.