informa
2 min read
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`))
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?

Latest Jobs

Treyarch

Playa Vista, California
6.20.22
Audio Engineer

Digital Extremes

London, Ontario, Canada
6.20.22
Communications Director

High Moon Studios

Carlsbad, California
6.20.22
Senior Producer

Build a Rocket Boy Games

Edinburgh, Scotland
6.20.22
Lead UI Programmer
More Jobs   

CONNECT WITH US

Register for a
Subscribe to
Follow us

Game Developer Account

Game Developer Newsletter

@gamedevdotcom

Register for a

Game Developer Account

Gain full access to resources (events, white paper, webinars, reports, etc)
Single sign-on to all Informa products

Register
Subscribe to

Game Developer Newsletter

Get daily Game Developer top stories every morning straight into your inbox

Subscribe
Follow us

@gamedevdotcom

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