Hola a todos,
Tenemos un esquema dentro de nuestra comunidad mediante el cual promovemos a ciertos usuarios habituales al estatus de PosBuddy. Estos son miembros regulares que comparten el tono de voz de nuestras marcas y han demostrado aptitud para responder preguntas, impulsar conversaciones y dar la bienvenida a nuevos miembros dentro de la comunidad.
Este esquema otorga a estos usuarios el nivel TL4; tienen su propio grupo privado dentro de la comunidad y la capacidad de cerrar, fusionar y mover publicaciones, manteniendo en general la comunidad ordenada. También brindamos a estos miembros muchos beneficios excelentes, como productos gratuitos, mercancía e invitaciones exclusivas a nuestras exhibiciones de desarrollo. Como pueden imaginar, hay grandes incentivos para ascender hasta TL3 y contribuir a nuestra comunidad.
A menudo queremos saber qué usuarios están casi en TL4, pero aún no lo han alcanzado; para ello, revisamos cientos de usuarios TL2 cada mes para ver qué tan lejos están de alcanzar TL4 y verificamos sus requisitos en la tabla de Nivel de Confianza 4.
Queremos facilitar este proceso, por lo que contamos con una consulta del explorador de datos que muestra la mayor parte de la información que necesitamos en una sola vista. Sin embargo, no logro averiguar cómo incluir los siguientes campos en la consulta que se muestra a continuación:
- Temas a los que se respondió
- Publicaciones señaladas
- Usuarios que señalaron
- Me gusta recibidos: días únicos
- Me gusta recibidos: usuarios únicos
- Silenciado (todo el tiempo)
- Suspensión (todo el tiempo)
Si alguien puede ayudar, le estaría eternamente agradecido.
-- [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 "Visitas",
coalesce(topics_viewed,0) as "Temas Vistos",
coalesce(posts_read,0) as "Publicaciones Leídas",
coalesce(posts_created,0) as "Publicaciones Creadas",
coalesce(topics_created,0) as "Temas Creados",
coalesce(topics_with_replies,0) as "Temas con Respuestas",
coalesce(likes_given,0) as "Me Gusta Dados",
coalesce(likes_received,0) as "Me Gusta Recibidos"
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