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