Cohort Ranks — leaderboards bucketed by year of first comp

Ranking

Bucket every competitor by the year of their first competition (cohort), then rank within each bucket by their lifetime best per event. The "2014 cohort" is everyone whose first WCA registration was in 2014 — and where they currently stand on 333 single relative to peers from that year.

Answers "fastest of their year" — local ranking within the cohort rather than the global leaderboard. With a country param the rank becomes a within-country rank inside the cohort; without it, world rank.

By the numbers

~10 M

Rows

cohort × event × single/average × person

2003+

Cohort range

From the WCA-revival year; the lone 1982 comp is not a cohort here

生涯累积

Ranking metric

Lifetime PB to date — not best-within-year

wr / cr

Two ranks stored

world_rank + country_rank precomputed — country toggle = re-pick column

Data source

CI first scans results JOIN competitions to derive each person's first_comp_date. In the main event loop, lifetime-best per person gets bucketed by cohort and sorted; assignRanks() handles ties (equal value → equal rank). Result lands in wca_cohort_ranks(cohort_year, event_id, is_avg, wca_id, value, country_id, world_rank, country_rank).

sql

SELECT cr.wca_id, cr.value, cr.country_id, co.iso2,
       cr.world_rank, cr.country_rank, p.name
FROM wca_cohort_ranks cr
JOIN wca_persons p ON p.wca_id = cr.wca_id
LEFT JOIN wca_countries co ON co.id = cr.country_id
WHERE cr.cohort_year = ?
  AND cr.event_id = ?
  AND cr.is_avg = ?
  /* AND cr.country_id = ? */
ORDER BY cr.world_rank ASC, cr.wca_id ASC
LIMIT ? OFFSET ?;

Algorithm / pipeline

Derive each person's first_comp_date

SELECT r.person_id, r.competition_id, c.start_date FROM results r JOIN competitions c ORDER BY c.start_date, r.competition_id

, then keep the first row per person → firstComp.get(pid).year is the cohort.

Carry forward lifetime PB

The main loop iterates results per event (sorted by start_date, id), maintaining Acc { best, avg, country } per (event, person), updating on strict improvement. Net result: every person's lifetime single PB / average PB.

Bucket by cohort, rank

For each (cohortYear, persons_in_cohort) group: sort by val ASC, then assignRanks() assigns both world_rank (over the full list) and country_rank (per-country running counter). Ties share a rank but the running counter keeps stepping.

At query time, sort by world_rank or country_rank

/v1/wca/cohort-ranks?cohort=&event=&type=&country= rides either coh_world or coh_country. With country set, ORDER BY country_rank plus country_id = ?; otherwise ORDER BY world_rank.

Caveats & edges

Cohort = year of first comp, unrelated to birth year or WCA-ID issue date — someone whose first comp is 2018 stays in the 2018 cohort even if their WCA-ID was issued in 2019.
Lifetime cumulative = up to today, NOT confined to the cohort year — "fastest of 2010" uses today's PB, not their 2010-season best.
333mbf has no average — type=average returns 400, same as all-results.
Ties share a rank (assignRanks() compares prevVal); PG ORDER BY adds wca_id ASC for stable pagination.

Cohort Ranks — leaderboards bucketed by year of first comp

By the numbers

Data source

Algorithm / pipeline

Caveats & edges

Related stats & links