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

<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:** [May 3, 2018, 1:37pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/1 "2018-05-03T13:37:18Z")

</div>

I’m trying to build a query for a leaderboard comprising the things that matter to us during the last month:

- Likes received
- Topics solved
- Badges gained

I started with the [User Participation](https://meta.discourse.org/t/user-participation/275012) query, stripped out what I didn’t want/need, and then started with adding `badges gained`. The badges gained query by itself looks like:

```sql

-- [params] 
-- string :interval = 1 month 
-- string :trunc = month 

    SELECT user_id, count(id) 
    FROM user_badges 
    WHERE granted_at &gt; date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) 
         AND granted_at &lt; date_trunc(:trunc, CURRENT_TIMESTAMP) 
    GROUP BY user_id 
    ORDER BY count DESC

```

That works just fine, but then when I try to convert it into the modified User Participation query, that’s where my head explodes:

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

```

I’m getting the following error (and I haven’t even started trying to add in Topics Solved):

```plaintext
    PG::UndefinedColumn: ERROR: column "badges" does not exist
    LINE 65: coalesce(badges,0) as badges_recv ^

```

Which doesn’t really make sense to me since badges is clearly identified… So I am a bit stuck… Any help finishing this query with adding `badges received` and `topics solved` would be appreciated!

---

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