Ich versuche, eine Abfrage für eine Rangliste zu erstellen, die die für uns wichtigsten Faktoren des letzten Monats berücksichtigt:
- Erhaltene Likes
- Gelöste Themen
- Erhaltene Abzeichen
Ich habe mit der Abfrage User Participation begonnen, das Entfernt, was ich nicht brauche, und dann mit dem Hinzufügen von badges gained (erhaltene Abzeichen) begonnen. Die Abfrage für badges gained allein sieht so aus:
-- [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
Das funktioniert einwandfrei, aber wenn ich versuche, sie in die modifizierte „User Participation"-Abfrage umzuwandeln, explodiert mir der Kopf:
-- [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
Ich erhalte folgenden Fehler (und ich habe noch nicht einmal begonnen, „Topics Solved" hinzuzufügen):
PG::UndefinedColumn: ERROR: column "badges" does not exist
LINE 65: coalesce(badges,0) as badges_recv ^
Das ergibt für mich keinen Sinn, da „badges" eindeutig definiert ist… Also stecke ich etwas fest… Jede Hilfe beim Fertigstellen dieser Abfrage durch Hinzufügen von badges received (erhaltene Abzeichen) und topics solved (gelöste Themen) wäre sehr willkommen!