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 …

[image] jerdog: left join users on pr.user_id = users.id I think you just need to add a join in there: left join b on b.user_id = users.id

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

ok so to come back to this as I have moved down the path a fair bit… trying to restrict to only a specific group, and then to all but a specific group (two separate queries). -- User participation -- [params] -- string :interval = 1 month -- string :trunc = month with t as ( select date_trunc(:t…

pinging @simon to see if he has any thoughts here

[image] jerdog: trying to restrict to only a specific group, and then to all but a specific group (two separate queries). I think you could create another WITH query to get the users who belong to a group, and then do an inner join to the in_group table in your final query. This should exclud…

So I added the following based on group ID instead of name in_group AS ( SELECT gu.user_id FROM group_users gu JOIN groups g ON g.id = gu.group_id WHERE g.id = 41 ) and then added in the left join left join in_group on in_group.user_id = users.id and the same list appears - didn’…

[image] jerdog: and then added in the left join Using a LEFT JOIN is the problem here. You need to use an INNER JOIN so that only records that match in both tables are returned. An INNER JOIN can be written as just JOIN. This is a helpful article: A Visual Explanation of SQL Joins .

Doh… I hate joins… =) Thanks mate!

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)…

It is not a weird error, the code has a Syntax Error. Maybe you can see the issue better this way? ( (coalesce(likes_received,0) * 2) + (coalesce(replies,0) * 1) + (coalesce(badges1,0) * 3) ) + (coalesce(badges2,0) * 2) ) + (coalesce(badges3,0) * 1) ) as total_points

DOH! I missed my parentheses… thanks!

Attempting to create Monthly Leaderboard query - head exploding

Community Building Data & reporting

jerdog (Jeremy M) October 5, 2018, 2:15pm 15

@simon do you have any thoughts on the above?

Topic		Replies	Views
The default badge queries Data & reporting sql-triggered-badge , reference	1	899	August 27, 2023
What is the bug reporter badge? Data & reporting sql-triggered-badge	33	9525	September 17, 2023
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5522	November 12, 2021
Badge for posts with Likes from a specific group Data & reporting sql-triggered-badge	24	829	August 27, 2023
Find the users which are more likely to become TL3 Data & reporting sql-query , trust-levels	21	4167	February 12, 2026

Attempting to create Monthly Leaderboard query - head exploding

Related topics