Best round

Round

best_round finds rounds (one row = one competition × round_type) with the smallest sum of the top 3 results in that round — these are the legendary rounds where a cluster of competitors all posted elite times back-to-back.

BLD events (333bf / 444bf / 555bf / 333mbf) rank by single; all other events rank by average. 333mbf gets a separate transform path because its WCA value encoding is lower-is-better but the natural ranking is by points descending.

By the numbers

Events

One section per event, top 10 rounds each

top 3

Per round

`ROW_NUMBER ≤ 3` keeps the top three

single / average

Rank field

BLD uses best; others use average

points DESC

`333mbf` special

Queried separately, sorted by points sum descending

Data source

Runs parameterized SQL per event (event_id = '%s'). A window function ROW_NUMBER() OVER (PARTITION BY competition_id, round_type_id ORDER BY <best | average>) numbers competitors inside each round; MAX(CASE WHEN row_num = k THEN ...) pivots into "kth-place" columns. The 1st/2nd/3rd results sum to result_sum, sorted ascending, LIMIT 10.

sql

SELECT competition_id, round_type_id, event_id,
  MAX(CASE WHEN row_num = 1 THEN best_result END) first_result,
  MAX(CASE WHEN row_num = 2 THEN best_result END) second_result,
  MAX(CASE WHEN row_num = 3 THEN best_result END) third_result,
  first_result + second_result + third_result AS result_sum
FROM (
  SELECT competition_id, round_type_id, event_id, person_id,
    CASE WHEN event_id IN ('333bf','444bf','555bf','333mbf')
         THEN best ELSE average END AS best_result,
    ROW_NUMBER() OVER (
      PARTITION BY competition_id, round_type_id
      ORDER BY CASE WHEN event_id IN ('333bf','444bf','555bf','333mbf')
                    THEN best ELSE average END
    ) AS row_num
  FROM results
  WHERE event_id = '%s' AND best_result > 0
) ranked
GROUP BY competition_id, round_type_id
ORDER BY result_sum
LIMIT 10;

Algorithm / pipeline

Pick the rank field

BLD trio + multi-BLD rank by best (single, lower is better); all other events rank by average (the official ao5 mean). A single CASE expression handles both the ORDER BY and the pivot column.

Rank within each round

ROW_NUMBER() OVER (PARTITION BY competition_id, round_type_id ORDER BY <rank field>) numbers each row within its round (1, 2, 3, ...). Only positive values are considered (best_result > 0, dropping DNF/DNS/unattempted).

Pivot to wide table

MAX(CASE WHEN row_num = 1 THEN person_id END) first_id etc. — collapses the long table to one row per round with three columns each for the top three solvers. third_result IS NOT NULL drops rounds where fewer than three completed.

Sum, sort, top 10

result_sum = first + second + third, ordered ascending, LIMIT 10, one section per event.

`333mbf` separate transform path

Multi-BLD value is encoded lower-is-better (a single packed number for attempted / time / solved), but humans rank it by points descending. queryResults skips 333mbf; toJson queries it separately and sorts by sum of points descending before merging.

Caveats & edges

BLD events rank by single, all others by average — fixed by event, not user-configurable.
Rounds with fewer than 3 finishers are filtered (third_result IS NOT NULL). Niche events at tiny competitions may have zero rows.
333mbf displays a points sum, not a WCA-value sum — a different unit from clock-format times, so don't cross-compare.
A first-round and a final from the same competition can both appear (they have distinct round_type_ids).

Best round

By the numbers

Data source

Algorithm / pipeline

Caveats & edges

Related stats & links