One number, four answers: reconciling four divergent hit-rate pipelines over 46,139 settled predictions

Every analytics platform eventually ships a number nobody can trace. Ours was the hit rate. It appeared in four places — the model table, the leaderboard, the per-competition rollup, the daily stats cron — and they disagreed by as much as 17 percentage points on the same model, on the same day, reading the same database. This is that story, with the numbers. Every figure below is the output of a query you can re-run.

The symptom

The dashboard, the model profile page, and the leaderboard each showed a different rate for the same model — one a confident 100% while the model's own detail page showed 43%. Both pages rendered cached columns: neither was wrong at the SQL level, both were wrong as statements about reality. Four writers each owned a slightly different definition of hit, denominator, and in-scope rows, and nobody had written down which was authoritative, so all four survived.

What the source of truth actually says

The only table that holds one row per prediction, with the outcome attached, is ai_predictions. Everything else is a derived cache. Query the base table, count settled rows, and count hits:

-- S0: the only honest denominator SELECT count(*) AS settled_rows, count(*) FILTER (WHERE hit IS TRUE) AS hits, count(*) FILTER (WHERE hit IS NULL) AS unsettled_flag_nulls, round(100.0 * count(*) FILTER (WHERE hit IS TRUE) / count(*), 2) AS hit_rate_incl_nulls, round(100.0 * count(*) FILTER (WHERE hit IS TRUE) / nullif(count(*) FILTER (WHERE hit IS NOT NULL), 0), 2) AS hit_rate_excl_nulls FROM ai_predictions WHERE settled = true; 

Result:

settled_rows | hits | unsettled_flag_nulls | hit_rate_incl_nulls | hit_rate_excl_nulls 46,139 | 26,232| 121 | 56.85 | 57.01 

121 rows are flagged settled = true but carry a NULL hit, which alone moves the headline by 0.16 points. And the same table holds a second hit column, hit_1x2, disagreeing with hit on 162 rows (26,070 vs 26,232). A third: exact directional agreement, prediction_direction = actual_result, yields 17,022 — 36.89%. Same rows, same settlement timestamp, three defensible definitions, three numbers twenty points apart.

Part of that gap is by design. The platform emits single-direction predictions (32,423 rows, 58.12% hit) and double-direction leans (13,687 rows, 53.85%). Exact-match scoring marks every double a miss unless one specific side lands; any-covered-side scoring credits either. A metric that does not declare its shape is not a metric.

The four pipelines

Read "rows in scope" before "rate". S1 and S2 are each missing ~5,100 settled predictions. S3 covers 802 rows — 1.74% of the corpus — and S5's "global" row covers 3,916 rows (8.49%). A metric describing 1.74% of the platform is a sample without a sample size.

The four gap sources, in order of impact:

1. Definition drift. S4 lands exactly on 26,070 — the hit_1x2 count, not the hit count of 26,232. The rollup and the base table never measured the same event, and it stayed invisible because both round to "about 57%".

2. Denominator drift. S1 and S2 were backfilled at different moments, so 5,129 and 5,188 settled rows never entered their counters. A counter incremented on write and never reconciled always lags by the rows that bypass the increment: batch settlement, retry, manual re-grade.

3. Coverage drift. S3 keeps the top 150 models; S5 keeps whatever the cron reached. Neither states coverage, so a consumer reads "59.48%" as a platform claim.

4. Survivorship. 2,708 of 8,392 model rows are retired and own 7,843 settled predictions — 17.0% of the corpus — and the leaderboard excludes them. Dropping losers before averaging is the fastest way to manufacture a better hit rate, and it happens by filtering, not lying.

What the leaderboard looks like from the base table

-- S3 vs. truth, per model WITH truth AS ( SELECT model_id, count(*) AS n, count(*) FILTER (WHERE hit IS TRUE) AS h FROM ai_predictions WHERE settled = true GROUP BY model_id ) SELECT l.rank, m.name, l.total_predictions AS cached_n, l.hit_rate AS cached_rate, t.n AS true_n, round(100.0 * t.h / t.n, 2) AS true_rate FROM leaderboard l JOIN ai_models m ON m.id = l.model_id JOIN truth t ON t.model_id = l.model_id ORDER BY l.rank LIMIT 10; 

Top of the board:

rank | model_id | cached_n | cached_rate | true_n | true_rate 1 | 25183 | 7 | 100.0 | 21 | 42.86 2 | 25954 | 10 | 80.0 | 77 | 49.35 4 | 25915 | 5 | 100.0 | 18 | 61.11 6 | 25148 | 8 | 75.0 | 57 | 64.91 

Rank 1 holds seven predictions in the cache and twenty-one in reality: displayed at 100%, scores 42.86%. Across all 132 leaderboard models that join to settled data, 131 have a cached total_predictions that disagrees with the base table, and the mean absolute deviation between cached and true rate is 17.08 percentage points. That is not rounding. It is a ranking replaced by a memory of a ranking.

Per-model divergence, measured

WITH truth AS ( SELECT model_id, count(*) AS n, count(*) FILTER (WHERE hit IS TRUE) AS h FROM ai_predictions WHERE settled = true GROUP BY model_id ) SELECT count(*) AS models_compared, count(*) FILTER (WHERE abs(100.0 * t.h / t.n - m.dir_hit_rate) > 0.5) AS off_by_half_point, round(avg(abs(100.0 * t.h / t.n - m.dir_hit_rate)), 2) AS mean_abs_error_pp, round(max(abs(100.0 * t.h / t.n - m.dir_hit_rate)), 2) AS max_abs_error_pp FROM ai_models m JOIN truth t ON t.model_id = m.id WHERE m.dir_total_predictions > 0 AND t.n >= 50; 

models_compared | off_by_half_point | mean_abs_error_pp | max_abs_error_pp 147 | 54 | 0.86 | 13.33 

A third of models are off by more than half a point; the worst by 13.33. Against prediction_hit_stats it degrades further: of 111 comparable model rows, 68 are off by more than half a point and 89 carry a total_predictions that does not equal the true count — 80% wrong denominators, worst-case rate error 73.33 points.

Worst individual case, 美洲国家联赛·凌统: cached 60 predictions at 60.00%, actually 60 predictions at 73.33%. Same denominator, eight missing hits in the numerator — what a settlement path writing hit_1x2 while the counter reads hit looks like from outside.

The reconciliation job

The fix is not a migration: declare one source authoritative, make every other number a checked derivative.

import psycopg2 TRUTH_SQL = """ WITH truth AS ( SELECT model_id, count(*) AS n, count(*) FILTER (WHERE hit) AS h FROM ai_predictions WHERE settled = true AND hit IS NOT NULL GROUP BY model_id ) SELECT m.name, m.dir_total_predictions AS cached_n, m.dir_hit_rate AS cached_rate, t.n AS true_n, round(100.0 * t.h / t.n, 2) AS true_rate, abs(COALESCE(m.dir_hit_rate, 0) - round(100.0 * t.h / t.n, 2)) AS drift_pp FROM ai_models m JOIN truth t ON t.model_id = m.id WHERE m.dir_total_predictions > 0 AND t.n >= 50 ORDER BY drift_pp DESC; """ with psycopg2.connect(DSN) as conn, conn.cursor() as cur: cur.execute(TRUTH_SQL) rows = cur.fetchall() hard = [r for r in rows if float(r[5]) > 1.0] print(f"compared={len(rows)} drift>1.0pp={len(hard)} " f"coverage={sum(r[1] for r in rows) / sum(r[3] for r in rows):.2%}") # gate: refuse to publish the leaderboard when the cache has drifted assert not hard, f"{len(hard)} models exceed 1.0pp drift; rebuild blocked" 

The invariant that makes this enforceable is not "the cache equals the base table" — it is monotonic agreement: for every model, dir_total_predictions may only grow, may never exceed the base-table count, and must reach equality after each settlement batch. A counter behind is lagging; a counter ahead is double-counting. Both are detectable, which is the whole point — before this invariant existed, neither was.

Governance rules that actually stopped the drift

One table owns the event. ai_predictions is the only place a hit is decided. Every downstream number must be reconstructible from it in one statement, by someone who has never read the application code. A metric that cannot be reconstructed is not a metric — it is an opinion with a percentage sign.

Every derived table declares its coverage. 57.00% over 3,916 rows is fine as long as the row says 3,916 and the UI renders "n=3,916". It is not fine as a headline. Coverage is part of the value, not metadata.

Every metric names its definition and arity policy. Scoring a double-direction lean under exact-match rules silently penalizes 29.7% of the corpus. hit_direction_exact and hit_direction_covered are two columns, not one column with two histories.

Retired models stay in the denominator. Report the all-model rate and the active-only rate side by side, always both, always labeled. Removing 7,843 settled predictions changes the story more than any model improvement shipped this quarter. And every cached rate renders its age: a 100% hit rate from seven predictions computed three weeks ago should look as fragile as it is.

What this cost, honestly

No prediction was wrong, no settlement was wrong. The damage was that four numbers existed — each individually traceable — and the platform was willing to show whichever one was convenient. That is more dangerous than a bug: a bug gets fixed, a comfortable number gets defended.

The state: 46,139 settled predictions, 26,232 directionally correct under hit — 56.85% inclusive of 121 NULL-flagged rows, 57.01% excluding them. 8,392 model rows: 2,231 active holding 31,247 of those predictions, 2,708 retired holding 7,843. Cached counters disagree with the base table on 131 of 132 leaderboard rows and 54 of 147 model rows above the 50-prediction threshold.

Those are the numbers this platform will defend. Every other number now carries a label saying where it came from.

Analytics and research content. Not betting advice. 18+

本内容仅供数据分析与研究参考，不构成任何决策建议。