Requête Data Explorer pour afficher les progrès vers les exigences du niveau de confiance 3

[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

Souvent, nous souhaitons savoir quels utilisateurs sont presque au niveau de confiance 3 (TL3), mais pas encore tout à fait là.

J’ai rencontré un problème similaire aujourd’hui sur un site où je tentais de déterminer quels paramètres du site tl3 requires empêchaient la promotion des utilisateurs. Voici une requête qui peut être utilisée à cet effet. Elle utilise des requêtes similaires à celles employées par Discourse pour vérifier si un utilisateur doit être promu. Les exigences du niveau TL3 sont toutes disponibles en tant que paramètres de requête. Elles sont définies sur leurs valeurs par défaut.

Si le paramètre show all results (afficher tous les résultats) est coché, tous les résultats seront renvoyés. S’il est décoché, seuls les résultats des utilisateurs qui obtiendraient le statut TL3 avec les valeurs définies dans les autres paramètres de la requête seront renvoyés.

La requête est longue. Si quelqu’un la teste et remarque des erreurs, veuillez me le faire savoir. Les noms des colonnes sont longs pour faciliter le débogage. Ils devraient probablement être raccourcis.

--[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 || ' jours')::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 || ' jours')::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 || ' jours')::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 jours_visites
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at > CURRENT_DATE -  (:tl_time_period || ' jours')::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 nombre_reponses_topics
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 || ' jours')::interval
GROUP BY p.user_id
),
topics_viewed AS (
SELECT
tv.user_id,
COUNT(tv.user_id) AS nombre_vues_topics
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 || ' jours')::interval
GROUP BY tv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(posts_read) AS posts_lus
FROM user_visits uv
JOIN tl3_candidates c ON c.user_id = uv.user_id
WHERE visited_at >= CURRENT_DATE -  (:tl_time_period || ' jours')::interval
GROUP BY uv.user_id
),
num_flagged_posts AS (
SELECT
p.user_id,
COUNT(DISTINCT pa.post_id) AS nombre_posts_signales
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 || ' jours')::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 nombre_utilisateurs_signaleurs
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 || ' jours')::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 vues_topics_total
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_lus_total
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 nombre_likes_donnes
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 || ' jours')::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 nombre_likes_recus,
COUNT(DISTINCT acting_user_id) AS nombre_utilisateurs_likes_recus,
COUNT(DISTINCT ua.created_at::date) AS nombre_jours_likes_recus
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 || ' jours')::interval
AND t.archetype = 'regular'
AND ua.action_type = 2
GROUP BY ua.user_id
),
candidate_results AS(
SELECT
c.user_id,
COALESCE(jours_visites, 0) AS jours_visites,
COALESCE(jours_visites, 0) >= :tl_requires_days_visited AS critere_visites_respecte,
COALESCE(nombre_reponses_topics, 0) AS nombre_reponses_topics,
COALESCE(nombre_reponses_topics, 0) >= :tl_requires_topics_replied_to AS critere_reponses_respecte,
COALESCE(nombre_vues_topics, 0) AS nombre_vues_topics,
COALESCE(nombre_vues_topics, 0) >= (SELECT * FROM min_topics_viewed) AS critere_vues_topics_respecte,
COALESCE(posts_lus, 0) AS posts_lus,
COALESCE(posts_lus, 0) >= (SELECT * FROM min_posts_read) AS critere_posts_lus_respecte,
COALESCE(nombre_posts_signales, 0) AS nombre_posts_signales,
COALESCE(nombre_posts_signales, 0) <= :tl_requires_max_flagged AS critere_posts_signales_respecte,
COALESCE(nombre_utilisateurs_signaleurs, 0) AS nombre_utilisateurs_signaleurs,
COALESCE(nombre_utilisateurs_signaleurs, 0) <= :tl_requires_max_flagged AS critere_signaleurs_respecte,
COALESCE(vues_topics_total, 0) AS vues_topics_total,
COALESCE(vues_topics_total, 0) >= :tl_requires_topics_viewed_all_time AS critere_vues_topics_total_respecte,
COALESCE(posts_lus_total, 0) AS posts_lus_total,
COALESCE(posts_lus_total, 0) >= :tl_requires_posts_read_all_time AS critere_posts_lus_total_respecte,
COALESCE(nombre_likes_donnes, 0) AS nombre_likes_donnes,
COALESCE(nombre_likes_donnes, 0) >= :tl_requires_likes_given AS critere_likes_donnes_respecte,
COALESCE(nombre_likes_recus, 0) AS nombre_likes_recus,
COALESCE(nombre_likes_recus, 0) >= :tl_requires_likes_received AS critere_likes_recus_respecte,
COALESCE(nombre_utilisateurs_likes_recus, 0) AS nombre_utilisateurs_likes_recus,
COALESCE(nombre_utilisateurs_likes_recus, 0) >= :tl_requires_likes_received::float / 4.0 AS critere_utilisateurs_likes_recus_respecte,
COALESCE(nombre_jours_likes_recus, 0) AS nombre_jours_likes_recus,
COALESCE(nombre_jours_likes_recus, 0) >= (SELECT * FROM min_likes_received_days) AS critere_jours_likes_recus_respecte

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 critere_visites_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_reponses_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_vues_topics_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_posts_lus_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_posts_signales_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_signaleurs_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_vues_topics_total_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_posts_lus_total_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_likes_donnes_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_likes_recus_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_utilisateurs_likes_recus_respecte END
AND CASE WHEN  :show_all_results THEN true ELSE critere_jours_likes_recus_respecte END
ORDER BY jours_visites DESC