Ciao a tutti,
Nella nostra comunità abbiamo un programma con cui promuoviamo alcuni utenti regolari allo status di PosBuddy. Si tratta di membri abituali che condividono il tono di voce del nostro brand e hanno dimostrato attitudine nel rispondere alle domande, stimolare le conversazioni e accogliere i nuovi membri.
Questo programma concede a questi utenti il livello TL4: dispongono di un gruppo privato all’interno della comunità e hanno la possibilità di chiudere, unire e spostare i post, contribuendo generalmente a mantenere l’ordine nella community. Offriamo inoltre a questi membri numerosi vantaggi, come prodotti gratuiti, merchandising e inviti esclusivi alle nostre presentazioni di sviluppo. Come potete immaginare, ci sono forti incentivi per lavorare al fine di raggiungere il livello TL3 e contribuire alla nostra comunità.
Spesso abbiamo bisogno di sapere quali utenti sono quasi al livello TL3, ma non ancora del tutto, per cui ogni mese esaminiamo centinaia di utenti TL2 per valutare quanto siano vicini al raggiungimento del TL3 e verificare i requisiti indicati nella tabella Trust Level 3.
Vogliamo rendere questo processo più semplice, quindi abbiamo creato una query di Data Explorer che mostra la maggior parte delle informazioni necessarie in un’unica visualizzazione. Tuttavia, non riesco a capire come includere i seguenti campi nella query riportata di seguito:
- Argomenti a cui ha risposto
- Post segnalati
- Utenti che hanno segnalato
- Mi piace ricevuti: giorni unici
- Mi piace ricevuti: utenti unici
- Silenziato (totale)
- Sospeso (totale)
Se qualcuno potesse aiutarmi, ne sarei infinitamente grato.
-- [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 "Visite",
coalesce(topics_viewed,0) as "Argomenti visualizzati",
coalesce(posts_read,0) as "Post letti",
coalesce(posts_created,0) as "Post creati",
coalesce(topics_created,0) as "Argomenti creati",
coalesce(topics_with_replies,0) as "Argomenti con risposte",
coalesce(likes_given,0) as "Mi piace dati",
coalesce(likes_received,0) as "Mi piace ricevuti"
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
visite desc,
post letti desc,
post creati desc