Featured Blog

Writing a Weekly Leaderboard in SQL and Grails

Leaderboards can actually be tricksy things - here are some details on an implementation for a small scale project.

[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`))
FROM game_score
    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

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

def query = "...";

scores = session.createSQLQuery(query)

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


Explore the
Advertise with
Follow us

Game Developer Job Board

Game Developer


Explore the

Game Developer Job Board

Browse open positions across the game industry or recruit new talent for your studio

Advertise with

Game Developer

Engage game professionals and drive sales using an array of Game Developer media solutions to meet your objectives.

Learn More
Follow us


Follow us @gamedevdotcom to stay up-to-date with the latest news & insider information about events & more