Bonjour à tous,
Nous avons un dispositif au sein de notre communauté qui permet de promouvoir certains membres réguliers au statut de PosBuddy. Il s’agit de membres réguliers qui partagent le ton de voix de nos marques et qui ont fait preuve d’une aptitude à répondre aux questions, à stimuler les conversations et à accueillir les nouveaux membres au sein de la communauté.
Ce dispositif accorde à ces utilisateurs le niveau TL4 ; ils disposent de leur propre groupe privé au sein de la communauté et ont la possibilité de fermer, fusionner et déplacer des publications, contribuant ainsi généralement à maintenir la communauté bien organisée. Nous offrons également à ces membres de nombreux avantages, tels que des produits gratuits, de la marchandise et des invitations exclusives à nos présentations de développement. Comme vous pouvez l’imaginer, il existe de fortes incitations à gravir les échelons jusqu’au niveau TL3 et à contribuer à notre communauté.
Souvent, nous souhaitons savoir quels utilisateurs sont presque au niveau TL3, mais pas encore tout à fait là. Pour ce faire, nous examinons chaque mois des centaines d’utilisateurs de niveau TL2 pour évaluer à quel point ils sont proches du niveau TL3 et vérifier leurs exigences selon le tableau des niveaux de confiance 3.
Nous souhaitons faciliter ce processus, nous disposons donc d’une requête d’explorateur de données qui affiche la plupart des informations dont nous avons besoin dans une vue unique. Cependant, je n’arrive pas à déterminer comment inclure les champs suivants dans la requête ci-dessous :
- Sujets auxquels il a été répondu
- Messages signalés
- Utilisateurs ayant signalé
- J’aime reçus : jours uniques
- J’aime reçus : utilisateurs uniques
- Mis en silence (toutes périodes confondues)
- Suspendu (toutes périodes confondues)
Si quelqu’un peut aider, je lui en serai éternellement reconnaissant.
-- [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 "Visites",
coalesce(topics_viewed,0) as "Sujets consultés",
coalesce(posts_read,0) as "Messages lus",
coalesce(posts_created,0) as "Messages créés",
coalesce(topics_created,0) as "Sujets créés",
coalesce(topics_with_replies,0) as "Sujets avec réponses",
coalesce(likes_given,0) as "J'aime donnés",
coalesce(likes_received,0) as "J'aime reçus"
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