# ניסיון ליצור שאילתת טבלת מובילים חודשית - הראש מתפוצץ
**URL:** https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [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:** 20
**Page:** 1
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [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")
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 > 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:
```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!
---
### Author:  [@mcwumbly](https://meta.discourse.org/u/mcwumbly)
#### Post date: [3 במאי, 2018, 1:41pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/2 "2018-05-03T13:41:37Z")
> [@jerdog](#):
>
> left join users on pr.user\_id = users.id
I think you just need to add a join in there:
```sql
left join b on b.user_id = users.id
```
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [3 במאי, 2018, 1:43pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/3 "2018-05-03T13:43:55Z")
Ahh hell yes! Thanks! Now I need to figure out adding the topics solved to this
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [9 ביולי, 2018, 3:08pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/4 "2018-07-09T15:08:05Z")
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(: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
),
r as (
SELECT p.user_id, count(p.id) as replies
FROM posts p, t, pr
WHERE p.post_type = 1 AND
p.post_number > 1 AND
(p.created_at > t.start and
p.created_at < t.end) and
p.user_id IN
(select u.id
from users u
where u.primary_group_id = 41) AND
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),
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_recv,
coalesce(replies,0) as replies_made,
coalesce(badges,0) as badges_recv,
((coalesce(likes_received,0) * 2) + (coalesce(replies,0) * 1) + (coalesce(badges,0) * 3)) 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 b on b.user_id = users.id
left join r on r.user_id = users.id
order by
total_points desc NULLS LAST
```
Not sure where I would put the bits for that?
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [9 ביולי, 2018, 10:10pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/5 "2018-07-09T22:10:12Z")
pinging @simon to see if he has any thoughts here
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [10 ביולי, 2018, 12:53am UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/6 "2018-07-10T00:53:48Z")
> [@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 any records that don’t have a user\_id in the in\_group table
```plaintext
-- returns user_ids for a named group
WITH in_group AS (
SELECT
gu.user_id
FROM group_users gu
JOIN
groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
)
```
If you want another query that excludes the members of a group, you could do something like this
```plaintext
not_in_group AS (
SELECT
u.id
FROM users u
WHERE u.id NOT IN (SELECT * FROM in_group)
)
```
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [10 ביולי, 2018, 5:23pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/7 "2018-07-10T17:23:16Z")
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’t change anything.
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [10 ביולי, 2018, 5:33pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/8 "2018-07-10T17:33:26Z")
> [@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](https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/).
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [10 ביולי, 2018, 5:43pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/9 "2018-07-10T17:43:43Z")
Doh… I hate joins… =)
Thanks mate!
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [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")
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
>
> ```
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [27 באוגוסט, 2018, 3:09pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/11 "2018-08-27T15:09:47Z")
It is not a weird error, the code has a Syntax Error. Maybe you can see the issue better this way?
```plaintext
(
(coalesce(likes_received,0) * 2)
+ (coalesce(replies,0) * 1)
+ (coalesce(badges1,0) * 3)
)
+ (coalesce(badges2,0) * 2)
)
+ (coalesce(badges3,0) * 1)
) as total_points
```
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [27 באוגוסט, 2018, 3:19pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/12 "2018-08-27T15:19:19Z")
DOH! I missed my parentheses… thanks!
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [27 באוגוסט, 2018, 3:32pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/13 "2018-08-27T15:32:32Z")
No problem, many times it just takes a second pair of eyes to see something.
I’m a big fan of readability and don’t skimp on verbosity or newlines especially when things start to get gnarly looking. I rely heavily on indentation to keep track of nesting but many prefer to use an IDE or text editor that auto-magically adds closers as soon as a starter is added.
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [3 באוקטובר, 2018, 4:29pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/14 "2018-10-03T16:29:12Z")
And as I am continually refining this, I am trying to restrict posts/topics from certain categories from being included in the results, specifically categories 43, and 47-50 as seen in the query below, but they’re still included:
> **query**
>
> ```
> -- User participation
> -- [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
> ),
> r as (
> SELECT p.user_id, count(p.id) as replies
> FROM posts p, t, pr
> WHERE p.post_type = 1 AND
> p.post_number > 1 AND
> (p.created_at > t.start and
> p.created_at < t.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','43','47','48','49','50')
> GROUP BY p.user_id),
> b1 as (
> SELECT b1.user_id, count(1) as badges1
> FROM user_badges b1, t, pr
> WHERE granted_at between t.start and t.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, t, pr
> WHERE granted_at between t.start and t.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, t, pr
> WHERE granted_at between t.start and t.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
> ),
> in_group AS (
> SELECT gu.user_id
> FROM group_users gu
> JOIN groups g ON g.id = gu.group_id
> WHERE g.id = 41
> )
> 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
> join in_group on in_group.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
>
> ```
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [5 באוקטובר, 2018, 2:15pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/15 "2018-10-05T14:15:56Z")
@simon do you have any thoughts on the above?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [6 באוקטובר, 2018, 6:53pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/18 "2018-10-06T18:53:06Z")
I haven’t run the full query. The first thing I would do is check that all the WITH statements are returning the results you are looking for. You can check them in your final query by running something like `SELECT * FROM r`.
If the WITH statements are returning the correct results, but rows are being returned in your final query that you expect to be excluded, then the issue is with the joins in the query. If the final result should only include rows that are found in your `r` table, the problem will be the left join you are using for the `r` table. A left join will return the complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null. Try using an inner join instead (`JOIN` and `INNER JOIN` are the same thing.)
Here’s a good reference on joins: [A Visual Explanation of SQL Joins](https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/).
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [8 באוקטובר, 2018, 7:36pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/19 "2018-10-08T19:36:38Z")
Yeah so the WITH query looks to _maybe_ be the problem, as it is pulling every user regardless of the category the topic is in. For instance I have one user that it’s returning 9 posts but they are in those protected topic IDs (43, 47, 48, 49, 59):
```
r as (
SELECT p.user_id, count(p.id) as replies
FROM posts p, t, pr
WHERE p.post_type = 1 AND
p.post_number > 1 AND
(p.created_at > t.start and
p.created_at < t.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','43','47','48','49','50')
GROUP BY p.user_id),
```
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [8 באוקטובר, 2018, 9:42pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/20 "2018-10-08T21:42:57Z")
Are you sure the query is counting posts in the excluded topics and it’s not that the query is counting posts that are hidden, deleted, or not the current public\_version?
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [8 באוקטובר, 2018, 10:22pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/21 "2018-10-08T22:22:31Z")
It is counting posts in the excluded categories because one of the users has _only_ posted in those categories and yet still shows up.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [8 ביוני, 2024, 12:37pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/22 "2024-06-08T12:37:19Z")
This topic was automatically closed after 2227 days. New replies are no longer allowed.