'My' position in a leaderboard

Question

I have a situation where I need to retrieve the position of the current user in a regional leaderboard.

My tables look something like this:

Users
id | city | ...

Collected_Items
id | user_id | value | ...

Achievements
id | user_id | value | ...

So simply put, I'd like to retrieve the position in the leaderboard and the sum of the value of each collected_item as well as the sum of the value each achievement. Equal total values can be either ranked equally or based on the timestamp of collection (first to collect wins)

I have this so far: (adapted from this SO answer)

SELECT 
    user_id, 
    user_points
    FROM (
      SELECT 
        user_id,
        SUM(collected_items.value) AS 'user_points'
        FROM collected_items
        GROUP BY user_id
        ORDER BY SUM(collected_items.value) DESC
    ) AS T1
    GROUP BY T1.user_points
    ORDER BY T1.user_points DESC

Which produces results like

Results
user_id | user_points
2       | 61
1       | 1

Which is great, however I need to include the achievements, get the position of each user and also query by users.city (users only view leaderboards within their own city)

My ideal results would look like this:

Results
rank | user_id | value

How would I alter the above to produce the desired results? (I'm open to using PHP manipulation also)

Notes (for speed purposes):
- There are 101 collectable items
- There are 101 achievements
- There will be up to 250,000 users

What should be your position? Just number from `1` to `N`, increasing consecutive? — Alma Do, Mar 06 '14 at 13:15
where is the `position` field for each user? or it has to be automatically generated? — Federico J., Mar 06 '14 at 13:15
@Chococroc - `position` (or `rank`) should be generated by the query — ahren, Mar 06 '14 at 13:16

Alma Do · Accepted Answer · 2014-03-06T13:24:27.233

3

You can use just thing like

SELECT
  @i:=@i+1 AS rank,
  sub.*
FROM
  (SELECT 
    user_id, 
    user_points
    FROM (
      SELECT 
        collected_items.user_id,
        SUM(collected_items.value)+SUM(achievements.value) AS user_points
        FROM 
          collected_items
            INNER JOIN achievements ON collected_items.user_id=achievements.user_id
        GROUP BY collected_items.user_id
        ORDER BY user_points DESC
    ) AS T1
    GROUP BY T1.user_points
    ORDER BY T1.user_points DESC
  ) AS sub
  CROSS JOIN (SELECT @i:=0) AS init

edited Mar 06 '14 at 13:24

answered Mar 06 '14 at 13:19

Alma Do

37,009
9
76
105

How would I also include the values from `achievements` table? – ahren Mar 06 '14 at 13:21
@ahren Oh, missed that. If you want sum from both tables, use `JOIN` by `user_id`. I've updated. – Alma Do Mar 06 '14 at 13:25
Awesome - looks pretty good! I'll accept the best answer in the morning if it all works! (it's currently 2:30am here). – ahren Mar 06 '14 at 13:28
I get the `Query executed successfully!` message; but no results... Any ideas? – ahren Mar 06 '14 at 22:17
`INNER JOIN` fails? (no corresponding rows, so `NULL`s somewhere?) I'll be able to say something certain if you'll provide sample of real data (in sqlfiddle for example) – Alma Do Mar 07 '14 at 05:58
Execute the queries from inside to outside checking if it gives you results: First T1, then sub, and then the whole query – Federico J. Mar 07 '14 at 06:19
Other thing: I think it's not necesary ORDER BY inside T1, as you will GROUP and ORDER in the sub table (and the less ORDER, the better performance, ;D) – Federico J. Mar 07 '14 at 06:23

score 1 · Answer 2 · edited May 23 '17 at 10:29

1

Refered to this post, i would suggest:

SELECT 
    @s:=@s+1,
    user_id, 
    user_points
    FROM (SELECT @s:= 0),(
      SELECT 
        user_id,
        SUM(collected_items.value) AS 'user_points'
        FROM collected_items
        GROUP BY user_id
        ORDER BY SUM(collected_items.value) DESC
    ) AS T1
    GROUP BY T1.user_points
    ORDER BY T1.user_points DESC

edited May 23 '17 at 10:29

Community

1
1

answered Mar 06 '14 at 13:19

knx

398
3
21

How would I also include the values from `achievements` table? – ahren Mar 06 '14 at 13:20

score 1 · Answer 3 · answered Mar 06 '14 at 13:31

For getting additional fields, you only have to INNER JOIN against your user table again, and then, you have to order by the field users:

SELECT 
u.id, 
T2.*,
@i:=@i+1 AS rank

-- ... additional fields

FROM (
  SELECT * FROM (
      SELECT 
        user_id,
        SUM(collected_items.value) AS 'user_points'
        FROM collected_items
        GROUP BY user_id
        ORDER BY SUM(collected_items.value) DESC
    ) AS T1
    GROUP BY T1.user_points, T1.      
 ) T2
 INNER JOIN users u
 ON T2.user_id = u.id

 -- ... More inner joins to get more data...

 ORDER BY T2.user_points DESC

'My' position in a leaderboard

3 Answers3