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? ) has any thoughts about how to encourage folks to a spirit of community & thinking of / helping others…?
Data Explorer Query
For those who want the query:
with -- 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 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 ), -- 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 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 "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" 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