Data Explorer query to show progress towards trust level 3 requirements

[Kyle_Risi]

Hi Guys,

We have a scheme within our community in which we promote certain regulars to PosBuddy status, these are regulars who share our brands tone of voice and have show an aptitude for answering questions, driving conversation and welcoming new members within the community.

This scheme grants these users to TL4, they have their own private group within the community and have the ability to close, merge and move posts: generally keeping the community tidy. We also give these members lots of great benefits like free products, merch and exclusive invites to our development showcases. So as you can imagine there is great incentives to work their way up to TL3 and contribute to our community.

Often we will want to know which users are almost at TL3, but not quite there yet, to do this we go through hundreds of TL2 users each month and see how far they are away from reaching TL3 and check their requirements for Trust Level 3 table.

We want to make this process easier so we have a data explorer query which shows most of the information we need in a single view, however I can’t seem to work out how to include the following fields into the Query listed below:

• Topics Replied To
• Flagged Posts
• Users Who Flagged
• Likes Received: unique days
• Likes Received: unique users
• Silenced (All time)
• Suspended (All time)

If anyone could help I would be forever grateful.

-- [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

[simon]

@Kyle_Risi

종종 TL3에 거의 도달했지만 아직 완전히 도달하지 못한 사용자들이 누구인지 알고 싶어질 때가 있습니다.

오늘 제가 TL3 승급을 막고 있는 tl3 requires 사이트 설정이 무엇인지 찾아보려 했던 사이트에서 비슷한 문제를 겪었습니다. 이를 위해 사용할 수 있는 쿼리가 있습니다. 이 쿼리는 Discourse가 사용자가 승급되어야 하는지 확인할 때 사용하는 쿼리와 유사한 방식을 사용합니다. TL3 요구 사항은 모두 쿼리 매개변수로 사용할 수 있으며, 기본값으로 설정되어 있습니다.

show all results 매개변수가 체크되어 있으면 모든 결과가 반환됩니다. 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