percentile_cont, a 20,000 row window, and the difference between skipped and failed

Every score in CogniPrep is reported as a percentile, because a raw 74 out of 100 tells a candidate nothing. To do that you need a population distribution per game, and to keep that distribution current you need a job.

The first version of the job did what most first versions do: select the rows, compute in Node.

const sessions = await db.select().from(gameSessionsTable).where(eq(gameSessionsTable.game_id, gameId));
const scores = sessions.map((s) => s.raw_score);
// mean, median, stdDev, percentiles in JS

That selects every column, including a metrics jsonb blob that can be several kilobytes, for every session ever recorded for that game, in order to compute nine numbers. It works at a thousand rows and it is a timeout waiting to happen at a million.

The rewrite is one query

Postgres has ordered-set aggregates. percentile_cont computes a continuous (interpolated) percentile directly, and stddev_pop and avg are right there too:

const [agg] = await db
  .select({
    sampleSize: sql<number>`count(*)::int`,
    mean: sql<number>`avg(raw_score::numeric)`,
    stdDev: sql<number>`stddev_pop(raw_score::numeric)`,
    median: sql<number>`percentile_cont(0.5) within group (order by raw_score::numeric)`,
    p10: sql<number>`percentile_cont(0.10) within group (order by raw_score::numeric)`,
    p25: sql<number>`percentile_cont(0.25) within group (order by raw_score::numeric)`,
    p75: sql<number>`percentile_cont(0.75) within group (order by raw_score::numeric)`,
    p90: sql<number>`percentile_cont(0.90) within group (order by raw_score::numeric)`,
    // ...
  })
  .from(sql`(
    select ${gameSessionsTable.raw_score} as raw_score
    from ${gameSessionsTable}
    where ${eq(gameSessionsTable.game_id, gameId)}
    order by ${gameSessionsTable.completed_at} desc
    limit ${MAX_SAMPLE_SIZE}
  ) as recent_scores`);

Zero rows cross the wire. One row comes back with every statistic on it.

percentile_cont rather than percentile_disc matters for presentation. Discrete percentiles return an actual value from the dataset, which with a few hundred integer scores produces visibly chunky steps: your p75 and your p80 are the same number, and a candidate who improves by a point sees their percentile jump four places or not move at all. The continuous version interpolates between the two nearest ranked values, which reads as a real, smooth score.

Bound one: the window, and why it is large

const MAX_SAMPLE_SIZE = 20_000;

The subquery takes the most recent 20,000 scores rather than the whole history. Two reasons, and they pull in opposite directions.

Why bound at all. The aggregate's cost grows linearly with rows per game, and this runs under a 60 second function limit with a role-level statement_timeout behind it. Unbounded, it is fine today and gets slower forever, with no event that tells you the day it stopped being fine. The LIMIT also lets the existing (game_id, completed_at) index stop the scan early rather than reading the game's entire history and sorting all of it.

Why the bound is large rather than small. This is the number users are ranked against. At 20,000 the percentiles are effectively as stable as all-time. A small recency window (say the last few hundred) would make p90 swing night to night, and it would let whoever happened to practise most recently define the baseline everyone else is measured against. That is not a caching decision, it is a fairness decision that happens to be spelled as a constant.

If you take one thing from this post: a sample bound in a ranking system is a product parameter. Pick it from what makes the ranking stable, then confirm it is also fast, not the other way around.

Bound two: the floor, and the status it produces

const MIN_SAMPLE_SIZE = 10;

Below ten scores, a percentile is noise wearing a number's clothes. "You are in the 90th percentile" of eight people is a sentence that should not be printed.

The interesting part is not the threshold, it is what the job returns when it trips:

return {
  gameId,
  gameName,
  sampleSize,
  updated: false,
  skipped: true,
  error: `Insufficient data: ${sampleSize} scores (minimum ${MIN_SAMPLE_SIZE} required)`,
};

skipped: true, distinct from a failure. The run summary counts them separately:

export interface PopulationStatsRunSummary {
  results: GameStatsResult[];
  total: number;
  updated: number;
  /** Games with too little data. Expected, not an error. */
  skipped: number;
  /** Games that genuinely failed (query error, timeout). */
  failed: number;
}

We had recently added a batch of new providers, so a large share of games legitimately had almost no plays. Without the distinction, every nightly run reported dozens of "errors", and a job that always reports errors is a job nobody reads. The alert that fires constantly is worse than no alert, because it trains you to ignore the channel where the real failure will eventually appear.

Now: skipped is a count in a log line, failed is a Sentry report.

Never throwing, and why that needs a second half

updateGameStats never throws. A failure for one game is returned as updated: false so the other 164 still run, which is obviously right: one bad game should not deny everyone else a percentile update.

It is also how a job silently stops working. A function that swallows everything and a function that has nothing to do look identical from outside.

So the caller is explicitly responsible for surfacing failures, and the module imports two loggers on purpose:

import { logInfo, logError } from '../logger';
// Aliased to avoid colliding with the console logger above. This one reports to
// Sentry, which is what a silent-degradation failure needs.
import { captureError as logMonitoredError } from '../error-monitoring';

Per-game detail goes to logs. A run where failed > 0 goes to error monitoring. "Never throws" is only a safe design when paired with "and something else reports".

Summary

• Compute distributions in the database. percentile_cont ... within group (order by ...) is standard SQL and transfers no rows.
• Use the continuous variant when a human reads the output, so the numbers do not step.
• Bound the sample, and choose the bound for statistical stability rather than for speed.
• Separate "not enough data yet" from "this broke". They need different thresholds, different destinations and different reactions.

The percentiles this produces are what you see on the score pages behind the formats at https://cogniprep.app/tests. Play a numerical test, finish it, and the rank you get is one row from the query above.