요즘 신뢰 수준(Trust Level) 격차 보고서를 보고 있습니다. 특히 TL2와, 사용자가 TL1에서 TL2로 승격되지 못하게 하는 요인에 대해 살펴보고 있습니다.
사용자들의 격차를 살펴본 결과, 이 문제가 커뮤니티와 얼마나 관련이 깊은지 깨닫게 되었습니다. 단순히 내 질문과 내 의견에 대한 것이 아닙니다. 다른 사용자와의 교류도 중요합니다. 정말 좋은 점입니다.
하지만 많은 사용자가 TL2에 이토록 가깝게 다가와 있고, 단지 "기여(giving back)"만 부족한 상황이어서 다소 우울한 기분이 듭니다.
커뮤니티 정신과 타인을 생각하고 돕는 태도를 장려하는 방법에 대해 혹시 의견이 있으신 분이 계실까요? (2살짜리 아이의 부모님들처럼요? 
)
참고로:
Data Explorer 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