Sto cercando di costruire una query per una classifica che includa gli elementi che ci interessano nell’ultimo mese:
- Mi piace ricevuti
- Argomenti risolti
- Badge ottenuti
Ho iniziato con la query Partecipazione degli utenti, ho rimosso ciò che non volevo/necessitavo e poi ho iniziato ad aggiungere badge ottenuti. La query per i badge ottenuti da sola appare così:
-- [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
Funziona perfettamente, ma quando provo a convertirla nella query modificata di Partecipazione degli utenti, è lì che mi va in tilt la testa:
-- [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
Sto ricevendo il seguente errore (e non ho ancora nemmeno iniziato a provare ad aggiungere gli Argomenti Risolti):
PG::UndefinedColumn: ERROR: column "badges" does not exist
LINE 65: coalesce(badges,0) as badges_recv ^
Il che non ha molto senso per me, dato che badges è chiaramente identificato… Quindi sono un po’ bloccato… Qualsiasi aiuto per completare questa query aggiungendo badge ricevuti e argomenti risolti sarebbe apprezzato!