Data Explorer query to show progress towards trust level 3 requirements

Data & reporting
1

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.

image
image561×718 9.8 KB

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
5 Likes
2

I ran into something similar on a site today where I was trying to find which of the tl3 requires site settings were preventing users from being promoted. Here’s a query that can be used for that. It’s using similar queries to what Discourse uses when it checks to see if a user should be promoted. The TL3 requirements are all available as query parameters. They are set to their default values.

If the show all results parameter is checked, all results will be returned. If the show all results parameter is unchecked, only results for users who would be granted TL3 status with the values you have set in the query’s other parameters will be returned.

The query is long. If anyone tries it and notices errors, please let me know. The column names are long to make it easier to debug. They should probably be shortened.

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