Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Database Structure for a leaderboard with trends

I am developing a game like app with an leaderboard and want the leaderboard to have this features:

  • Show Top 100
  • Show Top 100 Monthly, Weekly, Daily
  • Show Trends next to every User (if the user moved up on the leaderboard show green up arrow, and if down then red down arrow)

Currently I have two tables:

user:

besides all userspecific data the table also has a points column

Query to get alltime TOP 100:

SELECT uuid, points FROM user ORDER BY points DESC LIMIT 100

user_scores:

COLUMNS: id, uuid, user_uuid, game_uuid, score_change, timestamp

Here a few Pseudo Example Rows:

|id|    uuid   |    user_uuid   |   game_uuid    | score_change|ts|
|--|-----------|----------------|----------------|-------------|--|
|1 | some_uuid | uuid_of_user_a | uuid_of_game_a |      2      |22|
|2 | some_uuid | uuid_of_user_a | uuid_of_game_b |      1      |24|
|3 | some_uuid | uuid_of_user_a | uuid_of_game_c |      3      |28|

after every game the score change of a user gets written into here.

Query to get e.g. the weekly leaderboard:

SELECT SUM(score_change) AS total, user_uuid 
FROM user_scores WHERE timestamp > week_begin & timestamp < week_end
GROUP BY user_uuid ORDER BY total DESC LIMIT 100

Of course i could also query the alltime TOP 100 out of the user_scores table, but i thought this is probably more efficient, because i don’t have to fetch through the whole user_scores table.

So far so good. But how do implement the trends of the ranks?

Tags:

3

Answers


  1. 0

    You should add timestamp column to each score change.
    Calculate the score on fly.

    Login or Signup to reply.
  2. 0

    Using CTE’s and Window functions should be able to give you the output you’re looking for.

    Try the following code below:

    Current rankings and Previous rankings

    WITH current_rankings AS (
    SELECT 
        user_uuid, 
        RANK() OVER (ORDER BY SUM(score_change) DESC) AS current_rank
    FROM 
        user_scores
    WHERE 
        timestamp >= this_week_start AND timestamp < this_week_end
    GROUP BY 
        user_uuid
    LIMIT 100
),
previous_rankings AS (
    SELECT 
        user_uuid, 
        RANK() OVER (ORDER BY SUM(score_change) DESC) AS previous_rank
    FROM 
        user_scores
    WHERE 
        timestamp >= last_week_start AND timestamp < last_week_end
    GROUP BY 
        user_uuid
    LIMIT 100
)

    Combine to determine trends

    SELECT 
    c.user_uuid,
    c.current_rank,
    p.previous_rank,
    CASE 
        WHEN c.current_rank < p.previous_rank THEN 'up'
        WHEN c.current_rank > p.previous_rank THEN 'down'
        ELSE 'same'
    END AS trend
FROM 
    current_rankings c
LEFT JOIN 
    previous_rankings p ON c.user_uuid = p.user_uuid;

    NOTE
    Adjust this_week_start, this_week_end, last_week_start, and last_week_end to your timestamp calculation logic

    Login or Signup to reply.
  3. 0

    You can left-join the previous week’s result, each set row-numbered by the total, and then subtract the row-numbers from each other to get the trend.

    You need to think about what tyep of ranking you want: ROW_NUMBER RANK or DENSE_RANK.

    SELECT
  this.total,
  this.user_uuid,
  this.rn AS current_position,
  this.rn - prev.rn AS trend
FROM (
    SELECT
      SUM(us.score_change) AS total,
      us.user_uuid,
      RANK() OVER (ORDER BY SUM(us.score_change) DESC) AS rn
    FROM user_scores us
    WHERE us.timestamp >= week_begin
      AND us.timestamp < week_end
    GROUP BY
      us.user_uuid
    ORDER BY
      total DESC
    LIMIT 100
) this
LEFT JOIN (
    SELECT
      SUM(us.score_change) AS total,
      us.user_uuid,
      RANK() OVER (ORDER BY SUM(us.score_change) DESC) AS rn
    FROM user_scores us
    WHERE us.timestamp >= prev_week_begin
      AND us.timestamp < week_begin
    GROUP BY
      us.user_uuid
    ORDER BY
      total DESC
    LIMIT 100  -- remove LIMIT to include trend for users who were > 100 previously
) prev ON prev.user_uuid = this.user_uuid;

    You are right that filtering by timestamp will improve performance, but you would need proper indexing, something like (timestamp, user_uuid) INCLUDE (score_change).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search