Current world records by country
CountryDoes not trust the historical regional_single_record = WR marker (which sticks even after the WR is broken). Instead recomputes the current best per event and credits its holder's country.
A cuber holding both single + average WR contributes 2; multiple holders in one country roll up under that country.
By the numbers
single + average
Two record types
Up to 2 active WRs per event
`e.rank < 900`
Active events only
Discontinued events excluded
`MIN(best/average)`
WR check
Global min per event = current WR
GROUP_CONCAT
Per-country join
Country's WR holders rendered as list
Data source
Two-step nested query: inner GROUP BY person_id, event_id for per-person PB, another GROUP BY event_id for the global WR, inner-join where PB = WR to find current holders. Single and average each run separately, UNIONed, then joined to persons for country, finally aggregated by country_id.
events.rank < 900 strips discontinued events (magic / mmagic / 333mbo / 333ft); WCA assigns those rank ≥ 900.
sql
-- single WR holders (average mirrors this)
SELECT DISTINCT ps.person_id
FROM (SELECT person_id, event_id, MIN(best) AS pb
FROM results WHERE best > 0
GROUP BY person_id, event_id) ps
JOIN (SELECT event_id, MIN(best) AS wr
FROM results WHERE best > 0
GROUP BY event_id) wr
ON ps.event_id = wr.event_id AND ps.pb = wr.wr
JOIN events e ON e.id = ps.event_id AND e.rank < 900;Algorithm / pipeline
1
Compute per-person PB
GROUP BY person_id, event_id with MIN(best), filter best > 0 to drop DNFs. Average mirrors via MIN(average).2
Compute global WR
Same
results table, GROUP BY event_id + MIN(best) — one WR value per event.3
Match holders
Inner join
PB = WR extracts current holders; ties (multiple cubers at the same time) all qualify.4
UNION single + average
Merge both subqueries — a cuber holding single + average WRs appears twice, contributing 2.
5
Roll up by country
COUNT(*) for the country's WR tally; GROUP_CONCAT(DISTINCT person.name ...) for the holder list.Caveats & edges
- "Current" means as of the dump snapshot (refreshed monthly); a new WR shows up only after the next dump.
- Tied WRs (multiple holders at the same value) all count, so an event can credit 2-3 countries simultaneously.
- Discontinued events (
e.rank ≥ 900) are dropped entirely — those "permanent WRs" don't appear here. - Different from
world_records_by_country— that counts all-time WR history; this counts only what's currently held.