So @simon I have another question around this… I am breaking things down for badges, giving different weight to which badge type… and getting a weird error when I try to add things together:
PG::SyntaxError: ERROR: syntax error at or near "+"
LINE 97: ...sce(badges1,0) * 3)) + (coalesce(badges2,0) * 2)) + (coalesc...
^
I am using “+” elsewhere in that line and it’s fine, but as soon as I try to break it down it gives that error…
Here is the query (wall of text hidden by spoiler):
Query
-- User participation
-- [params]
-- string :trunc = Day
-- string :interval = 1 day
-- string :start = 2018-06-01 00:00:00.000000
-- string :end = 2018-06-30 23:59:59.999999
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
where posts_read > 0
and visited_at between :start and :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 post_actions
left join posts
on post_actions.post_id = posts.id
where post_actions.created_at between :start and :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
),
r as (
SELECT p.user_id, count(p.id) as replies
FROM posts p, pr
WHERE p.post_type = 1 AND
p.post_number > 1 AND
(p.created_at between :start and :end)
and p.user_id not in ('-1', '-2')
AND pr.user_id = p.user_id
AND p.topic_id not in ('4','24','26','32','33','34','35','36','37','38','39','40')
GROUP BY p.user_id),
b1 as (
SELECT b1.user_id, count(1) as badges1
FROM user_badges b1, pr
WHERE granted_at between :start and :end
AND pr.user_id = b1.user_id
AND b1.badge_id in (select id from badges where badge_type_id = 1)
GROUP BY b1.user_id
),
b2 as (
SELECT b2.user_id, count(1) as badges2
FROM user_badges b2, pr
WHERE granted_at between :start and :end
AND pr.user_id = b2.user_id
AND b2.badge_id in (select id from badges where badge_type_id = 2)
GROUP BY b2.user_id
),
b3 as (
SELECT b3.user_id, count(1) as badges3
FROM user_badges b3, pr
WHERE granted_at between :start and :end
AND pr.user_id = b3.user_id
AND b3.badge_id in (select id from badges where badge_type_id = 3)
GROUP BY b3.user_id
)
select pr.user_id,
username,
name,
email,
visits,
coalesce(likes_received,0) as likes_recv,
coalesce(replies,0) as replies_made,
coalesce(badges1,0) as badges1_recv,
coalesce(badges2,0) as badges2_recv,
coalesce(badges3,0) as badges3_recv,
((coalesce(likes_received,0) * 2) + (coalesce(replies,0) * 1) + (coalesce(badges1,0) * 3)) + (coalesce(badges2,0) * 2)) + (coalesce(badges3,0) * 1)) as total_points
from pr
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
left join b1 on b1.user_id = users.id
left join b2 on b2.user_id = users.id
left join b3 on b3.user_id = users.id
left join r on r.user_id = users.id
order by
total_points desc NULLS LAST