All Events Done — days from first comp to all 17 events checked off

Person

How long does it take, counting from a person's first WCA competition, to post at least one valid result (best > 0) in every one of the 17 currently-active events? days_to_complete is the earliest such "all done" date minus their first-comp date.

Default sort is days_to_complete ASC — fastest to full sweep. Incomplete people (is_done = FALSE) sort by done_count descending — how close to the finish line.

By the numbers

Target events

Current ACTIVE_EVENTS set — discontinued events not counted

~150 k

Rows

One row per person with any WCA result

best > 0

Done criterion

Any single valid result counts the event as done

max(date)

Achievement date = max of 17 firsts

Latest first-result among the 17 events seals the achievement

Data source

CI maintains per (person, event ∈ ACTIVE_EVENTS) a first_done_date. In the main loop, when r.best > 0, take min(curr, r.compDate). After scanning all events: each person has 17 dates → doneCount = non-empty count, isDone = (doneCount === 17), maxDate = max(those 17), days_to_complete = (maxDate − first_comp_date).

sql

SELECT aed.wca_id, aed.country_id, co.iso2,
       aed.done_count, aed.is_done,
       aed.first_comp_id, aed.first_comp_date,
       aed.achievement_comp_id, aed.achievement_comp_date,
       ac.name AS achievement_comp_name,
       aed.days_to_complete, aed.total_comp_count, p.name
FROM wca_all_events_done aed
JOIN wca_persons p ON p.wca_id = aed.wca_id
LEFT JOIN wca_competitions ac ON ac.id = aed.achievement_comp_id
WHERE aed.is_done = TRUE
ORDER BY aed.days_to_complete ASC, aed.wca_id ASC
LIMIT ? OFFSET ?;

Algorithm / pipeline

Resolve first-comp date and id

CI presweep: results JOIN competitions ORDER BY start_date, comp_id, keep the earliest row per pid → firstComp.compId / date / year. personCompSet simultaneously tracks all attended comps for total_comp_count.

17-slot first-done array

In the main loop, when r.best > 0 && eventIdx != null: fill personEventFirstDone[pid][eventIdx] if empty, or overwrite when r.compDate < curr. eventIdx comes from EVENT_INDEX, aligned with ACTIVE_EVENTS order.

Aggregate done_count + maxDate

After all events scanned, per pid the 17-element array yields doneCount = non-empty count, maxDate = latest of those dates, isDone = (doneCount === 17).

Locate the achievement comp

When isDone, scan personCompSet[pid] for any comp whose compInfo.startDate === maxDate → achievementCompId. Then daysToComplete = floor((maxDate − firstCompDate) / 86400000).

Two sort orders at query time

/v1/wca/all-events-done?onlyDone=&country=: onlyDone=1 (default) → days_to_complete ASC. onlyDone=0 (full set) → done_count DESC, days_to_complete ASC NULLS LAST. Covered by aed_done / aed_country_done.

Key formulae

Formula

days = max₍e ∈ 17₎ firstDone(p, e) − firstComp(p)

firstDone(p, e) = the comp date of person p's first best > 0 in event e; if any of the 17 is missing → undefined overall, with is_done = FALSE and days_to_complete IS NULL.

Caveats & edges

"17 events" is today's set (ACTIVE_EVENTS). Magic / Master Magic / 333ft / 333mbo and other discontinued events are out — no one historically had to clear 21 events to count as "full sweep".
Singles only — average not required. Friendly to blind / multi-blind (no average rows), otherwise 333mbf would lock everyone out.
First-valid takes the earliest valid result — someone who DNF'd 4x4 in 2008 and only solved valid in 2010 has firstDone[4x4] = 2010.
country filter uses current nationality (country_id), not nationality at first comp — citizenship changes follow.