Most competitions abroad

One terse result.country_id != competition.country_id does it. Each results row stores the cuber's country at that moment — so the "abroad" flag follows country changes naturally, as expected.

Top spots are typically small-country residents who travel globally; intra-EU hopping contributes too — three countries in a week isn't rare.

By the numbers

Data source

results result joined to competitions; WHERE result.country_id != competition.country_id = abroad. COUNT(DISTINCT competition_id) avoids multi-event double-counting.

Continental FMC virtual countries are filtered out — they have no geo, marking them "abroad" would skew things.

SELECT person_id, COUNT(DISTINCT competition_id) competitions_abroad
FROM results
JOIN competitions ON competitions.id = competition_id
WHERE result.country_id != competition.country_id
  AND competition.country_id NOT IN
      ('XA','XE','XF','XM','XN','XO','XS','XW')
GROUP BY person_id
ORDER BY competitions_abroad DESC
LIMIT 100

Algorithm / pipeline

Caveats & edges

• Country-changers: pre-change and post-change periods each contribute abroad counts against their then-current country — fair.
• A long-term resident in country B still flagged as A → all B comps mark abroad, even though B is the daily home.
• Compared against competition.country_id != result.country_id, not persons.country_id (current) — historical accuracy over present snapshot.

Related stats & links