Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

Project: Real-Time Leaderboard

Build a real-time game leaderboard in Flink: running scores, a global Top-N with changelog output, windowed daily boards, and an upsert sink, with runnable stand-ins.

A leaderboard looks simple: add up points per player and show the best ten. In a streaming system it raises good questions. Do you want all-time or per-day scores? How do you avoid sending a whole new board for every score event? What happens to the sink when a player drops out of the top ten? In this project you answer each of them with Flink SQL and DataStream code and run the logic as Java and SQL stand-ins.

Flink code is real and read-only; the runnable blocks are stand-ins. This project uses ideas from SQL Windows, Joins and Top-N and Dynamic Tables and Changelogs.

Requirements

Input: score events {player, points, ts}. Output: (1) a live all-time top 5, (2) a daily top 3 published when the day ends, both written to a table keyed by rank so a web page can read the current board.

flowchart LR
    E["Score events"] --> A["Sum points per player<br/>updating"]
    A --> T["Top-5 (ROW_NUMBER)<br/>updating"]
    T --> U[("Upsert sink<br/>key: rank")]
    E --> W["Tumbling day window<br/>sum per player"]
    W --> D["Top-3 per day<br/>append-only"]
    D --> H[("History table")]

Two branches from one source: a live updating board and a finished daily board.

Step 1: running totals and the Top-N pattern in SQL

First the totals: an updating aggregate with one row per player (a changelog like the one in Dynamic Tables and Changelogs). Then a Top-N on top of it, expressed with ROW_NUMBER().

SQL
CREATE VIEW totals AS
SELECT player, SUM(points) AS score
FROM scores
GROUP BY player;

CREATE TABLE leaderboard (
  rank_no BIGINT,
  player  STRING,
  score   BIGINT,
  PRIMARY KEY (rank_no) NOT ENFORCED
) WITH ('connector' = 'jdbc', 'url' = 'jdbc:mysql://db:3306/game', 'table-name' = 'leaderboard');

INSERT INTO leaderboard
SELECT rownum, player, score
FROM (
  SELECT player, score,
         ROW_NUMBER() OVER (ORDER BY score DESC) AS rownum
  FROM totals
)
WHERE rownum <= 5;

The Top-N operator keeps only the five best rows. When a new score pushes player X from rank 6 into rank 5, Flink emits an update for rank 5 and for the player it displaced. Because the sink is keyed by rank_no, each emitted row simply overwrites the old content of that rank, so the table always shows the current board. Note the nested query: Flink only recognizes the Top-N pattern in exactly this shape (ranking in a subquery, filter on the rank outside).

Step 2: the logic as a runnable simulation

The Java stand-in keeps running totals and recomputes the top 3 after each event, but prints only the changes, which is what an upsert changelog carries. Run it and notice how many events do not change the board at all.

Java Stand-in: running totals with a Top-3 changelog

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Events that do not alter the top three produce no output. This matters for cost: a game with millions of score events per minute but a stable top 5 writes only a handful of rows per minute to the database.

Step 3: a daily board with windows

An all-time board never forgets; a daily board is a windowed result. With a tumbling window table-valued function the aggregation is append-only: each day's scores are emitted once, when the watermark passes midnight, and the state for that day is freed. Top-N over windows uses the window columns in the partition:

SQL
SELECT *
FROM (
  SELECT window_start, player, score,
         ROW_NUMBER() OVER (PARTITION BY window_start ORDER BY score DESC) AS rownum
  FROM (
    SELECT window_start, player, SUM(points) AS score
    FROM TABLE(TUMBLE(TABLE scores, DESCRIPTOR(ts), INTERVAL '1' DAY))
    GROUP BY window_start, window_end, player
  )
)
WHERE rownum <= 3;

Run the equivalent on a fixed table below, with timestamps as day numbers so the arithmetic is clear.

SQL Daily Top-3 (portable SQL equivalent)

Press Run (or Ctrl + Enter) to see the result here.

Step 4: the DataStream alternative

Sometimes you want full control, for example a leaderboard that only publishes when the board changes, with a throttle. A KeyedProcessFunction on a single key (a constant key "board") holds a TreeMap of the top entries; per-player totals come from a first keyed stage. Everything funnels through one key, which makes that operator effectively parallelism 1. It is acceptable for small boards, but for large ones split by region first, compute local top-N in parallel and merge the small local boards in a second stage (the same two-phase idea as salted aggregation in Parallelism, Skew and Tuning).

Design questions to discuss

  1. Ties. Two players with the same score need a deterministic order (add player to ORDER BY), or the board flickers between runs.
  2. Corrections. If a cheater's points are revoked, you need a negative score event or a changelog source with deletes; the Top-N operator handles retractions correctly.
  3. Late events. In the windowed board a late event after the window closed is dropped unless you configure table.exec.emit.late-fire or allow lateness; for the all-time board lateness does not matter, only the final sums.
  4. Cold start. Reading history from the beginning produces a burst of updates; consider mini-batching (table.exec.mini-batch.enabled) so the sink sees fewer intermediate boards.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is the Top-N result updating?

    Rankings change as scores change, so rows for affected ranks are retracted or overwritten.
  2. 2. Why key the leaderboard sink by rank_no?

    An upsert by rank means the table always has exactly the current top N rows.
  3. 3. What distinguishes the daily board from the all-time board?

    A finished window yields final results that never change, so the sink only appends.
  4. 4. What is the downside of funnelling all players through one key to maintain a board?

    One key means one subtask. Use local top-N followed by a merge stage for large inputs.

Summary

  • A live leaderboard is a running aggregate plus a Top-N (ROW_NUMBER() ... WHERE rownum <= N) written to an upsert sink keyed by rank.
  • Only board changes produce output, so costs stay low when the top is stable.
  • Windowed boards (daily) are append-only and free their state when the window closes.
  • Handle ties deterministically, plan corrections with retractions, and avoid single-key bottlenecks by two-stage Top-N.

Next: Practice: Flink Review Exercises