Building a Leaderboard with MongoDB Aggregations

Maria
MongoDB
22 Sep, 2026

Leaderboards seem trivial until you build one. Showing the top ten players is a sort and a limit. But players also want to see their own rank ("you're #48,213"), the players just above and below them, and separate boards for today, this week, and all time. Ties need to be handled consistently, and the whole thing needs to stay fast while scores pour in.

MongoDB's aggregation framework has grown a set of tools that fit this problem well. $setWindowFields computes ranks with $rank and $denseRank, $topN and $bottomN pick the best entries per group, $dateTrunc buckets scores into daily or weekly periods, and $merge materializes the results into a collection you can query instantly.

This guide builds a leaderboard from raw score events through to a production-ready design: the data model, top-N queries, computing a player's rank, "around me" views, time-windowed boards, handling ties, and materializing rankings so reads stay fast at scale.

The Data Model

There are two common shapes, and most real systems use both.

Score events record every game played:

db.scores.insertMany([
  {
    playerId: "p_ada",
    game: "tetra",
    score: 18420,
    playedAt: ISODate("2026-09-22T07:10:00Z"),
  },
  {
    playerId: "p_bob",
    game: "tetra",
    score: 22110,
    playedAt: ISODate("2026-09-22T07:12:00Z"),
  },
  {
    playerId: "p_cy",
    game: "tetra",
    score: 22110,
    playedAt: ISODate("2026-09-22T07:30:00Z"),
  },
  {
    playerId: "p_ada",
    game: "tetra",
    score: 25900,
    playedAt: ISODate("2026-09-22T08:01:00Z"),
  },
  {
    playerId: "p_dee",
    game: "tetra",
    score: 9800,
    playedAt: ISODate("2026-09-21T19:45:00Z"),
  },
]);

Player bests store one document per player per board, holding their best score:

{
  _id: { game: "tetra", playerId: "p_ada" },
  name: "Ada",
  best: 25900,
  bestAt: ISODate("2026-09-22T08:01:00Z"),
  gamesPlayed: 2
}

Events are the source of truth and give you history and time-based boards. The bests collection is what the all-time leaderboard actually queries, because ranking one document per player is far cheaper than scanning every game ever played.

Keeping Bests Up to Date

Update the best score atomically whenever a game finishes, using $max so a lower score never overwrites a higher one:

async function recordScore(db, { game, playerId, name, score }) {
  const playedAt = new Date();
  await db.collection("scores").insertOne({ playerId, game, score, playedAt });

  await db.collection("bests").updateOne(
    { _id: { game, playerId } },
    {
      $max: { best: score },
      $inc: { gamesPlayed: 1 },
      $set: { name },
    },
    { upsert: true },
  );
}

$max only changes the field if the new value is greater, and it creates the field on upsert. Tracking bestAt precisely takes a pipeline update, since it should only change when best changes:

await db.collection("bests").updateOne(
  { _id: { game, playerId } },
  [
    {
      $set: {
        name,
        gamesPlayed: { $add: [{ $ifNull: ["$gamesPlayed", 0] }, 1] },
        bestAt: {
          $cond: [
            { $gt: [score, { $ifNull: ["$best", -1] }] },
            playedAt,
            "$bestAt",
          ],
        },
        best: { $max: [score, { $ifNull: ["$best", -1] }] },
      },
    },
  ],
  { upsert: true },
);

Within a single $set stage, every expression sees the document as it was before the stage, so bestAt compares against the old best even though best is also being updated.

Indexes

db.scores.createIndex({ game: 1, playedAt: -1, score: -1 });
db.bests.createIndex({ "_id.game": 1, best: -1, bestAt: 1 });

The bests index supports "top N for a game" as a pure index scan, and the bestAt suffix gives a deterministic tiebreaker.

The Top 10

With the bests collection and its index, the top of the board is a simple find:

db.bests
  .find(
    { "_id.game": "tetra" },
    { _id: 0, playerId: "$_id.playerId", name: 1, best: 1 },
  )
  .sort({ best: -1, bestAt: 1 })
  .limit(10);

The sort includes bestAt ascending, so when two players tie, whoever reached the score first ranks higher. Without a tiebreaker, MongoDB can return tied documents in any order, and players will see their positions flicker between page loads.

Top N Straight From Events

If you don't maintain a bests collection, you can compute the board from raw events. $group with $max gives each player's best, then sort and limit:

db.scores.aggregate([
  { $match: { game: "tetra" } },
  { $group: { _id: "$playerId", best: { $max: "$score" } } },
  { $sort: { best: -1, _id: 1 } },
  { $limit: 10 },
]);

This is fine for thousands of events and a cron-built board. For millions of events queried on every page view, it's far too slow, which is why the bests collection exists.

Computing Ranks with $setWindowFields

A sorted list implies rank by position, but you often need the rank as a number, including how ties are treated. $setWindowFields with $rank or $denseRank does exactly that:

db.bests.aggregate([
  { $match: { "_id.game": "tetra" } },
  {
    $setWindowFields: {
      partitionBy: "$_id.game",
      sortBy: { best: -1 },
      output: {
        rank: { $rank: {} },
        denseRank: { $denseRank: {} },
        position: { $documentNumber: {} },
      },
    },
  },
  {
    $project: {
      _id: 0,
      player: "$_id.playerId",
      best: 1,
      rank: 1,
      denseRank: 1,
      position: 1,
    },
  },
]);

[
  {
    "player": "p_ada",
    "best": 25900,
    "rank": 1,
    "denseRank": 1,
    "position": 1
  },
  {
    "player": "p_bob",
    "best": 22110,
    "rank": 2,
    "denseRank": 2,
    "position": 2
  },
  { "player": "p_cy", "best": 22110, "rank": 2, "denseRank": 2, "position": 3 },
  { "player": "p_dee", "best": 9800, "rank": 4, "denseRank": 3, "position": 4 }
]

The three operators differ only in how they treat ties:

Operator	Ties share a rank?	Next rank after a tie	Typical use
`$rank`	Yes	Skips (1, 2, 2, 4)	Sports-style "standard competition" ranking
`$denseRank`	Yes	No gap (1, 2, 2, 3)	Tiers, levels, "you're in the 3rd tier"
`$documentNumber`	No	Sequential (1, 2, 3, 4)	Strict positions using a tiebreaker

$rank and $denseRank require sortBy to have exactly one field, because they need a single value to decide what counts as a tie. If you want "ties broken by earliest time", use $documentNumber with a two-field sort instead.

"What's My Rank?"

Running $setWindowFields over a million players to find one player's rank is wasteful. A player's rank is just one plus the number of players with a strictly better score, and with the best index that count is fast:

async function rankOf(db, game, playerId) {
  const bests = db.collection("bests");
  const me = await bests.findOne({ _id: { game, playerId } });
  if (!me) return null;

  const better = await bests.countDocuments({
    "_id.game": game,
    best: { $gt: me.best },
  });
  return { playerId, best: me.best, rank: better + 1 };
}

This gives $rank semantics (ties share a rank). For strict positions with the time tiebreaker, count the players who either scored higher or scored the same earlier:

const ahead = await bests.countDocuments({
  "_id.game": game,
  $or: [
    { best: { $gt: me.best } },
    { best: me.best, bestAt: { $lt: me.bestAt } },
  ],
});

countDocuments still has to walk the matching index keys, so for a player ranked #800,000, it counts 800,000 keys. That's typically tens of milliseconds, which is often acceptable. When it isn't, materialize ranks (covered below) or show approximate ranks for players far down the board ("Top 35%").

The "Around Me" View

Players care most about the handful of people just above and below them. With the player's score in hand, two small indexed queries do it:

async function aroundMe(db, game, playerId, span = 3) {
  const bests = db.collection("bests");
  const me = await bests.findOne({ _id: { game, playerId } });
  if (!me) return [];

  const above = await bests
    .find({
      "_id.game": game,
      $or: [
        { best: { $gt: me.best } },
        { best: me.best, bestAt: { $lt: me.bestAt } },
      ],
    })
    .sort({ best: 1, bestAt: -1 })
    .limit(span)
    .toArray();

  const below = await bests
    .find({
      "_id.game": game,
      $or: [
        { best: { $lt: me.best } },
        { best: me.best, bestAt: { $gt: me.bestAt } },
      ],
    })
    .sort({ best: -1, bestAt: 1 })
    .limit(span)
    .toArray();

  return [...above.reverse(), me, ...below];
}

Combine it with rankOf to label each row: the first row's position is the player's rank minus above.length, and positions increase by one from there.

Daily and Weekly Leaderboards

Time-windowed boards come from the events collection. $dateTrunc buckets each score into its period, and $topN picks each player's best within that period:

db.scores.aggregate([
  {
    $match: {
      game: "tetra",
      playedAt: {
        $gte: ISODate("2026-09-21T00:00:00Z"),
        $lt: ISODate("2026-09-28T00:00:00Z"),
      },
    },
  },
  {
    $group: {
      _id: "$playerId",
      best: { $max: "$score" },
      bestGame: {
        $top: {
          sortBy: { score: -1, playedAt: 1 },
          output: { score: "$score", at: "$playedAt" },
        },
      },
      games: { $sum: 1 },
    },
  },
  { $sort: { best: -1, "bestGame.at": 1 } },
  { $limit: 100 },
]);

$top returns the first document of each group according to its own sortBy, so you get both the best score and when it happened. It's the single-result form of $topN.

For a report showing the top three players of every day in a month, $topN shines:

db.scores.aggregate([
  {
    $match: {
      game: "tetra",
      playedAt: { $gte: ISODate("2026-09-01"), $lt: ISODate("2026-10-01") },
    },
  },
  {
    $group: {
      _id: {
        day: {
          $dateTrunc: {
            date: "$playedAt",
            unit: "day",
            timezone: "Europe/Lisbon",
          },
        },
      },
      podium: {
        $topN: {
          n: 3,
          sortBy: { score: -1, playedAt: 1 },
          output: { playerId: "$playerId", score: "$score" },
        },
      },
    },
  },
  { $sort: { "_id.day": 1 } },
]);

[
  {
    "_id": { "day": "2026-09-21T23:00:00.000Z" },
    "podium": [
      { "playerId": "p_ada", "score": 25900 },
      { "playerId": "p_bob", "score": 22110 },
      { "playerId": "p_cy", "score": 22110 }
    ]
  }
]

The timezone option matters. "Today" means midnight in your players' time zone, not UTC, and the output date is the UTC instant of that local midnight, which is why the day shows as 23:00 the previous day in UTC. Note that $topN over raw events can include the same player more than once if they had several high-scoring games; group by player first if you need distinct players. Handling dates and time zones covers the time zone side in more depth.

For weekly boards, use unit: "week" with startOfWeek: "monday" (or whatever your game uses).

Materializing Leaderboards with $merge

Computing ranks on every request doesn't scale to millions of players and thousands of requests per second. The standard answer is to compute the board periodically and store the results, then serve reads from the stored copy. $merge writes aggregation output into a collection, updating existing documents in place:

db.bests.aggregate([
  { $match: { "_id.game": "tetra" } },
  {
    $setWindowFields: {
      sortBy: { best: -1 },
      output: { rank: { $rank: {} } },
    },
  },
  {
    $project: {
      _id: { board: "tetra:alltime", playerId: "$_id.playerId" },
      board: "tetra:alltime",
      playerId: "$_id.playerId",
      name: 1,
      best: 1,
      rank: 1,
      computedAt: "$$NOW",
    },
  },
  {
    $merge: {
      into: "leaderboard",
      on: "_id",
      whenMatched: "replace",
      whenNotMatched: "insert",
    },
  },
]);

With an index on the materialized collection, every read becomes a trivial lookup:

db.leaderboard.createIndex({ board: 1, rank: 1 });

// Page 3 of the board (ranks 101 to 150)
db.leaderboard
  .find({ board: "tetra:alltime", rank: { $gt: 100, $lte: 150 } })
  .sort({ rank: 1 });

// A player's rank
db.leaderboard.findOne({ _id: { board: "tetra:alltime", playerId: "p_ada" } });

Range queries on rank also give you efficient deep pagination without skip().

A few operational details:

Schedule it. Run the job every minute, every five minutes, or hourly depending on how fresh the board must be. A cron job, a worker, or an Atlas scheduled trigger all work.
Players who drop off. $merge doesn't delete documents that disappear from the output. If players can be removed (bans, account deletions), clean up entries whose computedAt is older than the latest run.
Hybrid freshness. Serve the top of the board and the materialized rank from the snapshot, and show the player's own live best score from the bests collection. Players see their new score instantly, and the rank catches up on the next refresh.
Big boards. $setWindowFields sorts the whole partition. For very large boards, set allowDiskUse: true (it's the default in recent versions) and consider running the job against a secondary or an analytics node.

For views that aren't worth materializing, a regular or on-demand materialized view might be enough; see views and on-demand materialized views.

Beyond One Board: Seasons and Friends

Seasons are just a different $match window on events, materialized into a board key like tetra:season-7. Keep old season boards around as a history.

Friends leaderboards filter bests by a list of player IDs:

const friendIds = ["p_bob", "p_cy", "p_ada"];
db.bests
  .find({ "_id.game": "tetra", "_id.playerId": { $in: friendIds } })
  .sort({ best: -1, bestAt: 1 });

Friend lists are small, so this is fast even without materialization.

Common Pitfalls

No tiebreaker in the sort. Tied players swap places between requests. Always add a deterministic second sort key such as bestAt or _id.

Ranking raw events on every request. Aggregating millions of score events per page view is slow and expensive. Maintain a one-document-per-player bests collection with $max.

Read-modify-write for best scores. Reading the old best, comparing in code, and writing the new one races with concurrent games. Use $max or a pipeline update.

Ignoring time zones on daily boards. UTC midnight isn't your players' midnight. Pass timezone to $dateTrunc.

Using skip() for deep pages. Page 5,000 of a board with skip walks 250,000 entries. Paginate on the materialized rank field instead.

Picking the wrong rank operator. $rank skips numbers after ties, $denseRank doesn't, $documentNumber never ties. Decide which one your players expect before launch, because changing it later confuses everyone.

Conclusion

A solid MongoDB leaderboard combines a few simple pieces: score events as the source of truth, a bests collection updated atomically with $max, indexed sorts with a tiebreaker for the top of the board, countDocuments for a player's rank, $setWindowFields for rank numbers with explicit tie semantics, $dateTrunc and $topN for daily and weekly boards, and $merge to materialize everything once traffic grows.

Load 100,000 fake score events into a local database, build the bests collection with a single $group plus $merge, and time rankOf for a player near the bottom. That number tells you whether you can serve ranks live or need the materialized board from day one.