# Attempting to create Monthly Leaderboard query - head exploding

**URL:** <https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [May 3, 2018, 1:37pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679 "2018-05-03T13:37:18Z")\
**Posts on this page:** 1\
**Showing post:** 14

<div class="post-metadata">

**Author:** ![jerdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jerdog/32/122843_2.png) [@jerdog](https://meta.discourse.org/u/jerdog)\
**Post date:** [October 3, 2018, 4:29pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/14 "2018-10-03T16:29:12Z")

</div>

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
> 
> ```

---

_[View the full topic](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679)._