Building a Game Leaderboard

Welcome back! In the last lesson, you learned how to make your word prediction game smarter by scoring guesses based on how close their meaning is to the correct answer. Now, let’s take your game to the next level by adding a leaderboard.

A leaderboard is a simple but powerful feature. It lets players see how their scores compare to others, which makes the game more exciting and competitive. In this lesson, you’ll learn how to store player scores and display the top results for each day. By the end, you’ll have the foundation for a daily leaderboard that updates as people play.

A table in an SQL database is like a spreadsheet where you store and organize related pieces of information. Each table has columns (which define the type of data you want to store, like names or scores) and rows (which hold the actual data entries).

For example, if you want to keep track of player scores in your game, you would create a table with columns for the player’s name, their score, and the date they played. Each time someone finishes a game, you add a new row to the table with their information.

Here’s how you might create a table for high scores in SQL:

CREATE TABLE IF NOT EXISTS HighScore (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  score INTEGER NOT NULL,
  date TEXT NOT NULL
)

• id: A unique number for each entry (automatically increases).
• name: The player’s name (must be filled in).
• score: The player’s score (must be filled in).
• date: The date the score was achieved.

Whenever you want to save or look up scores, you interact with this table—adding new rows for new scores, or reading rows to display the leaderboard. Tables are the foundation for storing and retrieving data in any SQL database.

Now, let’s build the structure for our leaderboard. We want to store each player’s name, their score, and the date they played. Let’s go step by step.

First, we need to import the right tools and set up our database connection:

const path = require('path');
const sqlite3 = require('sqlite3').verbose();

const dbPath = process.env.DATABASE_URL || path.join(__dirname, 'highscores.db');
const db = new sqlite3.Database(dbPath);

function run(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.run(sql, params, function (err) {
      if (err) return reject(err);
      resolve(this);
    });
  });
}

Next, let’s create our HighScore table if it doesn’t already exist:

async function ensureSchema() {
  await run(`CREATE TABLE IF NOT EXISTS HighScore (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    score INTEGER NOT NULL,
    date TEXT NOT NULL
  )`);
}

Let’s break down what each part does:

• id: This is a unique number for each score entry. It helps us keep track of each row.
• name: This stores the player’s name. It’s a string and cannot be empty.
• score: This is the player’s score. It must be a number and cannot be empty.
• date: This records the date the score was achieved. It’s stored as a string in the format "YYYY-MM-DD".

To make it easy to display scores, we can write a function that turns a score row into a JavaScript object:

function toScoreObject(row) {
  return {
    id: row.id,
    name: row.name,
    score: row.score,
    date: row.date
  };
}

This function helps us convert a score entry into a format that’s easy to use in our app or send to the frontend.

Now that we have a table, let’s see how to save a new score and fetch the top scores for a given day.

To save a score, we need a function that takes the player’s name, their score, and the date. Here’s how you can do it:

const dayjs = require('dayjs');

async function submitScore(name, score, dateStr) {
  const d = dateStr ? dayjs(dateStr).format('YYYY-MM-DD') : dayjs().format('YYYY-MM-DD');
  await run(
    `INSERT INTO HighScore (name, score, date) VALUES (?, ?, ?)`,
    [name, score, d]
  );
}

Let’s break this down:

• name and score are the player’s name and score.
• dateStr is an optional string like "2025-06-01". If it’s not given, we use today’s date.
• We insert a new row into the HighScore table with this data.

To show the leaderboard for a specific day, we need to get all scores for that date and sort them from highest to lowest. Here’s how:

function all(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => {
      if (err) return reject(err);
      resolve(rows);
    });
  });
}

async function getLeaderboard(dateStr) {
  const d = dateStr ? dayjs(dateStr).format('YYYY-MM-DD') : dayjs().format('YYYY-MM-DD');
  const rows = await all(
    `SELECT id, name, score, date FROM HighScore WHERE date = ? ORDER BY score DESC`,
    [d]
  );
  return rows.map(toScoreObject);
}

Here’s what’s happening:

• We figure out which date to use (either the one given or today).
• We ask the database for all HighScore entries with that date.
• We use ORDER BY score DESC in our SQL query to sort the results so that the highest scores appear first (DESC stands for "descending order").
• We turn each score into a JavaScript object for easy use.

Example Output

Suppose we have three scores for today:

If we call getLeaderboard(), we’ll get:

[
  { id: 1, name: "Alice", score: 90, date: "2025-06-01" },
  { id: 3, name: "Carol", score: 85, date: "2025-06-01" },
  { id: 2, name: "Bob", score: 75, date: "2025-06-01" }
]

This list is ready to be shown on your game’s leaderboard!

In this lesson, you learned how to create a leaderboard for your word prediction game. You built a HighScore table to store player names, scores, and dates. You also wrote functions to save new scores and fetch the top scores for any day.

These are the building blocks for a daily leaderboard that updates as people play. Up next, you’ll get to practice these skills by working with the leaderboard code yourself. This hands-on practice will help you get comfortable with saving and displaying scores in your own game. Good luck!