And as I am continually refining this, I am trying to restrict posts/topics from certain categories from being included in the results, specifically categories 43, and 47-50 as seen in the query below, but they’re still included:
query
-- User participation
-- [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
),
r as (
SELECT p.user_id, count(p.id) as replies
FROM posts p, t, pr
WHERE p.post_type = 1 AND
p.post_number > 1 AND
(p.created_at > t.start and
p.created_at < t.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','43','47','48','49','50')
GROUP BY p.user_id),
b1 as (
SELECT b1.user_id, count(1) as badges1
FROM user_badges b1, t, pr
WHERE granted_at between t.start and t.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, t, pr
WHERE granted_at between t.start and t.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, t, pr
WHERE granted_at between t.start and t.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
),
in_group AS (
SELECT gu.user_id
FROM group_users gu
JOIN groups g ON g.id = gu.group_id
WHERE g.id = 41
)
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
join in_group on in_group.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