大家好，
我们社区内有一个计划，将某些活跃成员提升为 PosBuddy 状态。这些成员认同我们品牌的语调，并展现出解答问题、推动对话以及欢迎新成员加入社区的能力。
该计划授予这些用户 TL4 权限。他们在社区内拥有自己的私密群组，并具备关闭、合并和移动帖子的能力，从而总体上保持社区的整洁。此外，我们还为这些成员提供诸多丰厚福利，例如免费产品、周边商品以及参加我们开发展示会的专属邀请。因此，可以想象，激励他们努力晋升至 TL3 并为社区做出贡献的动力是巨大的。
我们通常希望了解哪些用户已接近 TL3 但尚未完全达到要求。为此，我们每月会逐一审查数百名 TL2 用户，评估他们距离达到 TL3 还有多远，并核对他们在“信任等级 3 要求”表中的各项条件。
我们希望简化这一流程，因此我们编写了一个数据探索器查询，可在单一视图中展示大部分所需信息。然而，我似乎无法弄清楚如何将以下字段纳入下方的查询中：
- 回复的主题数
- 被标记的帖子数
- 标记用户
- 收到的点赞数：唯一天数
- 收到的点赞数：唯一用户数
- 被静默（累计）
- 被暂停（累计）
如果有任何人能提供帮助，我将不胜感激。
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 100
with
t as (
select
CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) as end
),
pr as (
select user_id,
count(1) as visits,
sum(posts_read) as posts_read
from user_visits, t
where posts_read > 0
and visited_at > t.start
and visited_at < t.end
group by user_id
),
pc as (
select user_id,
count(1) as posts_created
from posts, t
where created_at > t.start
and created_at < t.end
group by user_id
),
ttopics as (
select user_id, posts_count
from topics, t
where created_at > t.start
and created_at < t.end
),
tc as (
select user_id,
count(1) as topics_created
from ttopics
group by user_id
),
twr as (
select user_id,
count(1) as topics_with_replies
from ttopics
where posts_count > 1
group by user_id
),
tv as (
select user_id,
count(distinct(topic_id)) as topics_viewed
from topic_views, t
where viewed_at > t.start
and viewed_at < t.end
group by user_id
),
likes as (
select
post_actions.user_id as given_by_user_id,
posts.user_id as received_by_user_id
from t, post_actions
left join posts
on post_actions.post_id = posts.id
where post_actions.created_at > t.start
and post_actions.created_at < t.end
and post_action_type_id = 2
),
lg as (
select given_by_user_id as user_id,
count(1) as likes_given
from likes
group by user_id
),
lr as (
select received_by_user_id as user_id,
count(1) as likes_received
from likes
group by user_id
),
e as (
select email, user_id
from user_emails u
where u.primary = true
)
select pr.user_id,
coalesce(visits,0) as "Visits",
coalesce(topics_viewed,0) as "Topics Viewed",
coalesce(posts_read,0) as "Posts Read",
coalesce(posts_created,0) as "Posts Created",
coalesce(topics_created,0) as "Topics Created",
coalesce(topics_with_replies,0) as "Topics with Replies",
coalesce(likes_given,0) as "Likes Given",
coalesce(likes_received,0) as "Likes Received"
from pr
left join tv using (user_id)
left join pc using (user_id)
left join tc using (user_id)
left join twr using (user_id)
left join lg using (user_id)
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by
visits desc,
posts_read desc,
posts_created desc