Data Explorer 查询以显示达到信任等级 3 要求的进度

Kyle_Risi · 2019 年4 月 4 日 13:47

大家好，

在我们的社区中，我们推行了一项计划，将某些活跃成员提升为“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 "访问次数",
       coalesce(topics_viewed,0) as "浏览主题数",
       coalesce(posts_read,0) as "阅读帖子数", 
       coalesce(posts_created,0) as "创建帖子数",
       coalesce(topics_created,0) as "创建主题数",
       coalesce(topics_with_replies,0) as "有回复的主题数",
       coalesce(likes_given,0) as "给予点赞数",
       coalesce(likes_received,0) as "收到点赞数"
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

simon · 2020 年6 月 17 日 05:47

今天我在一个网站上遇到了类似的情况，当时我正试图找出哪些 tl3 requires 站点设置阻止了用户晋升。这里有一个可用于此目的的查询。它使用了与 Discourse 检查用户是否应晋升时类似的查询。TL3 要求均可作为查询参数使用，并已设置为默认值。

如果勾选了 show all results 参数，将返回所有结果。如果未勾选该参数，则仅返回那些根据您在查询其他参数中设置的值将被授予 TL3 状态的用户结果。

该查询较长。如果有人尝试后发现错误，请告诉我。列名较长是为了便于调试，可能应该缩短。

--[params]
-- int :tl_time_period = 100
-- int :tl_requires_days_visited = 50
-- int :tl_requires_topics_replied_to = 10
-- int :tl_requires_topics_viewed = 25
-- int :tl_requires_topics_viewed_cap = 500
-- int :tl_requires_posts_read = 25
-- int :tl_requires_posts_read_cap = 20000
-- int :tl_requires_max_flagged = 5
-- int :tl_requires_topics_viewed_all_time = 200
-- int :tl_requires_posts_read_all_time = 500
-- int :tl_requires_likes_given = 30
-- int :tl_requires_likes_received = 20
-- boolean :show_all_results = true

WITH tl3_candidates AS (
SELECT id AS user_id FROM users
WHERE trust_level = 2
AND last_seen_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
),
min_topics_viewed AS (
SELECT
LEAST(COUNT(*) * (:tl_requires_topics_viewed / 100.0), :tl_requires_topics_viewed_cap)  AS min_topics_viewed
FROM topics
WHERE visible = true
AND archetype = 'regular'
AND created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
),
min_posts_read AS (
SELECT
LEAST(COUNT(*) * (:tl_requires_posts_read / 100.0), :tl_requires_posts_read_cap)  AS min_posts_read
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
),
min_likes_received_days AS (
SELECT
LEAST(:tl_requires_likes_received::float / 3.0, 0.75 * :tl_time_period::float)
),
days_visited AS (
SELECT
uv.user_id,
COUNT(uv.user_id) AS days_visited
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at > CURRENT_DATE -  (:tl_time_period || ' days')::interval
AND posts_read >= 0
GROUP BY uv.user_id
),
num_topics_replied_to AS (
SELECT
p.user_id,
COUNT(DISTINCT p.topic_id) AS topic_reply_count
FROM posts p
JOIN topics t ON t.id = p.topic_id
JOIN tl3_candidates c ON c.user_id = p.user_id
WHERE p.user_id <> t.user_id
AND t.archetype <> 'private_message'
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND p.created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
GROUP BY p.user_id
),
topics_viewed AS (
SELECT
tv.user_id,
COUNT(tv.user_id) AS topic_view_count
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
JOIN tl3_candidates c ON c.user_id = tv.user_id
WHERE t.archetype <> 'private_message'
AND viewed_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
GROUP BY tv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(posts_read) AS posts_read
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
GROUP BY uv.user_id
),
num_flagged_posts AS (
SELECT
p.user_id,
COUNT(DISTINCT pa.post_id) AS num_flagged_posts
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN tl3_candidates c ON c.user_id = p.user_id
WHERE p.created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
AND (spam_count > 0 OR inappropriate_count > 0)
AND agreed_at IS NOT NULL
AND pa.user_id <> p.user_id
GROUP BY p.user_id
),
num_flagged_by_users AS (
SELECT
p.user_id,
COUNT(DISTINCT pa.user_id) AS num_flagged_by_users
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN tl3_candidates c ON c.user_id = p.user_id
WHERE p.created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
AND (spam_count > 0 OR inappropriate_count > 0)
AND agreed_at IS NOT NULL
AND pa.user_id <> p.user_id
GROUP BY p.user_id
),
topics_viewed_all_time AS (
SELECT
tv.user_id,
COUNT(topic_id) AS topics_viewed_all_time
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
JOIN tl3_candidates c ON c.user_id = tv.user_id
WHERE t.archetype = 'regular'
GROUP BY tv.user_id
),
posts_read_all_time AS (
SELECT 
uv.user_id,
SUM(posts_read) AS posts_read_all_time
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
GROUP BY uv.user_id
),
num_likes_given AS (
SELECT 
ua.user_id,
COUNT(*) AS num_likes_given
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id
JOIN tl3_candidates c ON c.user_id = ua.user_id
WHERE ua.created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
AND t.archetype = 'regular'
AND ua.action_type = 1
GROUP BY ua.user_id
),
num_likes_received AS (
SELECT 
ua.user_id,
COUNT(*) AS num_likes_received,
COUNT(DISTINCT acting_user_id) AS num_likes_received_users,
COUNT(DISTINCT ua.created_at::date) AS num_likes_received_days
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id
JOIN tl3_candidates c ON c.user_id = ua.user_id
WHERE ua.created_at >= CURRENT_DATE -  (:tl_time_period || ' days')::interval
AND t.archetype = 'regular'
AND ua.action_type = 2
GROUP BY ua.user_id
),
candidate_results AS(
SELECT
c.user_id,
COALESCE(days_visited, 0) AS days_visited,
COALESCE(days_visited, 0) >= :tl_requires_days_visited AS visits_criteria_met,
COALESCE(topic_reply_count, 0) AS topic_reply_count,
COALESCE(topic_reply_count, 0) >= :tl_requires_topics_replied_to AS replies_criteria_met,
COALESCE(topic_view_count, 0) AS topic_view_count,
COALESCE(topic_view_count, 0) >= (SELECT * FROM min_topics_viewed) AS topic_views_criteria_met,
COALESCE(posts_read, 0) AS posts_read,
COALESCE(posts_read, 0) >= (SELECT * FROM min_posts_read) AS posts_read_criteria_met,
COALESCE(num_flagged_posts, 0) AS num_flagged_posts,
COALESCE(num_flagged_posts, 0) <= :tl_requires_max_flagged AS flagged_post_criteria_met,
COALESCE(num_flagged_by_users, 0) AS num_flagged_by_users,
COALESCE(num_flagged_by_users, 0) <= :tl_requires_max_flagged AS flagged_by_users_criteria_met,
COALESCE(topics_viewed_all_time, 0) AS topics_viewed_all_time,
COALESCE(topics_viewed_all_time, 0) >= :tl_requires_topics_viewed_all_time AS all_time_topic_views_criteria_met,
COALESCE(posts_read_all_time, 0) AS posts_read_all_time,
COALESCE(posts_read_all_time, 0) >= :tl_requires_posts_read_all_time AS posts_read_all_time_criteria_met,
COALESCE(num_likes_given, 0) AS num_likes_given,
COALESCE(num_likes_given, 0) >= :tl_requires_likes_given AS likes_given_criteria_met,
COALESCE(num_likes_received, 0) AS num_likes_received,
COALESCE(num_likes_received, 0) >= :tl_requires_likes_received AS likes_received_criteria_met,
COALESCE(num_likes_received_users, 0) AS num_likes_received_users,
COALESCE(num_likes_received_users, 0) >= :tl_requires_likes_received::float / 4.0 AS likes_received_users_criteria_met,
COALESCE(num_likes_received_days, 0) AS num_likes_received_days,
COALESCE(num_likes_received_days, 0) >= (SELECT * FROM min_likes_received_days) AS likes_received_days_criteria_met

FROM tl3_candidates c
LEFT JOIN days_visited dv ON dv.user_id = c.user_id
LEFT JOIN num_topics_replied_to ntr ON ntr.user_id = c.user_id
LEFT JOIN topics_viewed tv ON tv.user_id = c.user_id
LEFT JOIN posts_read pr ON pr.user_id = c.user_id
LEFT JOIN num_flagged_posts nfp ON nfp.user_id = c.user_id
LEFT JOIN num_flagged_by_users nfu ON nfu.user_id = c.user_id
LEFT JOIN topics_viewed_all_time tvat ON tvat.user_id = c.user_id
LEFT JOIN posts_read_all_time prat ON prat.user_id = c.user_id
LEFT JOIN num_likes_given nlg ON nlg.user_id = c.user_id
LEFT JOIN num_LIkes_received nlr ON nlr.user_id = c.user_id
)

SELECT * FROM candidate_results
WHERE CASE WHEN :show_all_results THEN true ELSE visits_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE replies_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE topic_views_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE posts_read_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE flagged_post_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE flagged_by_users_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE all_time_topic_views_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE posts_read_all_time_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE likes_given_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE likes_received_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE likes_received_users_criteria_met END
AND CASE WHEN  :show_all_results THEN true ELSE likes_received_days_criteria_met END
ORDER BY days_visited DESC

话题		回复	浏览量
Find the users which are more likely to become TL3 Data & reporting sql-query , trust-levels	21	4228	2026 年2 月 12 日
Help with creating a query to replicate TL3 progress Data & reporting sql-query , trust-levels	0	134	2019 年4 月 4 日
Requirements for Trust Level 3 Data & reporting sql-query	0	84	2025 年12 月 15 日
How many TL3 users do you have? Do the TL3 requirements really make sense? Community Building	42	2791	2023 年3 月 12 日
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5565	2021 年11 月 12 日

Data Explorer 查询以显示达到信任等级 3 要求的进度

相关话题