# 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:** 10

<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:** [August 27, 2018, 2:26pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/10 "2018-08-27T14:26:45Z")

</div>

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

---

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