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 …

[画像] 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

[画像] 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 exclude a…

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

[画像] 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!

月次リーダーボードクエリの作成を試みるも頭が爆発しそう

コミュニティデータとレポート

jerdog (Jeremy M) 2018 年 10 月 5 日午後 2:15 15

@simon do you have any thoughts on the above?

トピック		返信	表示
The default badge queries Data & reporting sql-triggered-badge , reference	1	899	2023 年 8 月 27 日
What is the bug reporter badge? Data & reporting sql-triggered-badge	33	9525	2023 年 9 月 17 日
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5522	2021 年 11 月 12 日
Badge for posts with Likes from a specific group Data & reporting sql-triggered-badge	24	829	2023 年 8 月 27 日
Find the users which are more likely to become TL3 Data & reporting sql-query , trust-levels	21	4167	2026 年 2 月 12 日

月次リーダーボードクエリの作成を試みるも頭が爆発しそう

関連トピック