I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the player:

1. Hot leaderboard (periods: Day, Week, Month)

   - #rank (user's position in the leaderboard)
   - user_fk
   - won games count
   - lost games count

2. General leaderboard (Overall data)

   - #rank (user's position in the leaderboard)
   - user_fk
   - won games count
   - lost games count

As you can see, the models are the same, but the queries are different. Below you can find the database structure for the User model and for the Score logging.

User model

  • id
  • created_at/updated_at
  • picture
  • nickname
  • zone_id
  • xp
  • played games
    • James: win/lost count
    • Lisa: win/lost count
    • Jacob: win/lost count

As you can see, the user can play against 3 different opponent players: James, Lisa and Jacob. Every time the player finishes a game with an opponent player (CPU), the win or lost counts are incremented. Based on that, a new XP (experience point) is added or subtracted from the user's XP.

Score log model

  • uuid
  • user_id
  • date_created
  • against
  • won (true, false)
  • zone_id

For the hot leaderboard I'm trying to write the following query:

Select users ordered by number of won games DESC, lost games ASC,
where date_created <= 24h AND againts = OpponentPlayer AND zone_id = ZONE LIMIT 250

And for the General leaderboard:

Select users ordered by XP DESC, won games DESC, lost games ASC
where updated_at <= 90 days and zone_ID LIMIT 999

The third thing that I don't know how to solve is to: How to get a specific user's rank based on the general leaderboard?

  1. Is this the right approach to log user's played games?
  2. Should I create a different table for leaderboards and update their value once in a while?
  • Do you have MySQL 8.0 or MariaDB 10.2? – Rick James Jan 25 '19 at 1:37
  • I am using MySQL 8.0 version. – Zbarcea Christian Jan 25 '19 at 9:05
  • 2
    Study the "windowing functions", such as RANK() and DENSE_RANK() that are available in that version. – Rick James Jan 25 '19 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.