Estou tentando construir uma consulta para uma tabela de classificação com base no que é importante para nós no último mês:
- Curtidas recebidas
- Tópicos resolvidos
- Conquistas obtidas
Comecei com a consulta Participação do Usuário, removi o que não queria/necessitava e depois comecei a adicionar conquistas obtidas. A consulta de conquistas obtidas isoladamente parece assim:
-- [params]
-- string :interval = 1 month
-- string :trunc = month
SELECT user_id, count(id)
FROM user_badges
WHERE granted_at > date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval)
AND granted_at < date_trunc(:trunc, CURRENT_TIMESTAMP)
GROUP BY user_id
ORDER BY count DESC
Isso funciona perfeitamente, mas quando tento convertê-la na consulta modificada de Participação do Usuário, é aí que minha cabeça explode:
-- [params]
-- string :interval = 1 month
-- string :trunc = month
with
t as (
select date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) as start,
date_trunc(:trunc, CURRENT_TIMESTAMP) 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
),
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
),
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
),
b as (
SELECT b.user_id, count(1) as badges
FROM user_badges b, t, pr
WHERE granted_at > t.start
AND granted_at < t.end
AND pr.user_id = b.user_id
GROUP BY b.user_id
)
select pr.user_id,
username,
name,
email,
visits,
coalesce(likes_received,0) as likes_received,
coalesce(badges,0) as badges_recv
from pr
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by
likes_received desc,
visits desc
Estou recebendo o seguinte erro (e ainda nem comecei a tentar adicionar Tópicos Resolvidos):
PG::UndefinedColumn: ERROR: column "badges" does not exist
LINE 65: coalesce(badges,0) as badges_recv ^
O que não faz muito sentido para mim, já que badges está claramente identificado… Então estou um pouco travado… Qualquer ajuda para finalizar essa consulta adicionando conquistas recebidas e tópicos resolvidos seria muito apreciada!