Attempting to create Monthly Leaderboard query - head exploding


(Jeremy M (Jerdog)) #1

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 query, stripped out what I didn’t want/need, and then started with adding badges gained. The badges gained query by itself looks like:

-- [params] 
-- string :interval = 1 month 
-- string :trunc = month 
SELECT user_id, count(id) 
FROM user_badges 
WHERE granted_at > date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) 
     AND granted_at < 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:

-- [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):

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!


How do I see who is marking solved and on what?
(Dave McClure) #2

I think you just need to add a join in there:

left join b on b.user_id = users.id

(Jeremy M (Jerdog)) #3

Ahh hell yes! Thanks! Now I need to figure out adding the topics solved to this