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…?
For reference:
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