Writing a Weekly Leaderboard in SQL and Grails

[James posts his thoughts on Game Development at The Blocky Pixel]

I’ve spent the greater part of the day just trying to get a god damn leaderboard webservice up and going. I have now.

There are a few things you need to take into consideration when creating a leaderboard database. First up – what kind of leaderboard do you want? Secondly, how many users do you have?

If you want weekly leaderboards, all time best leaderboards and all time best scores, the simplest way to store that is just one monolithic table containing the user, the score and the date. The game can submit a score to the server, and it can just dump it in the leaderboard table.

Simple, eh?

Well, it is until you want to query it. This thing can get expensive fast, because your query isn’t super awesome. It is the most flexible though. Weekly leaderboards are the awesomest kind for user retention, and all-time-best scores are great for more ongoing player progression.

For anyone who cares, this is the query I’ve used.

CREATE TABLE `game_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `play_date` datetime NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `value` bigint(20) NOT NULL,
  PRIMARY KEY (`id`))

SELECT *
FROM game_score
INNER JOIN (
    SELECT user_id as highscore_user_id, max(value) as highscore
    FROM game_score
    WHERE play_date > "2011-10-20" AND play_date < "2011-10-29"
    GROUP BY user_id) as highscores
ON user_id = highscores.highscore_user_id
   AND value = highscores.highscore
GROUP BY user_id
ORDER BY value DESC
LIMIT 10;

I can update the date filter each week to get a weekly leaderboard, or remove it entirely and get an all time best leaderboard.

How well does this scale? Probably not very well. But I like to code first, optimise later. If I have millions of rows that need to be optimised into specialised tables, that’s a pretty good problem to have.

I’ve used this in Grails and bound it to some my entities by skipping GORM and jumping right into the hibernate session.

import org.codehaus.groovy.grails.commons.ApplicationHolder as AH;

...

def session = AH.application.mainContext
                .sessionFactory.currentSession;

def query = "...";

scores = session.createSQLQuery(query)
                .addEntity(GameScore.class).list();

Has anyone else had any experience with weekly leaderboard implementation? How have your solutions scaled with awesome success?