Most distinct dates competed on
PersonExplode multi-day comps day-by-day — SQL cross-joins an inline nums (0..9) table to enumerate every concrete date a comp covers (start_date + n ≤ end_date), then distincts by MM/DD. Which calendar days have you ever competed on?
Cubers on this leaderboard typically cover most months of the year; the TS side then groups dates by month and shows per-month coverage % — instantly readable for "summer-heavy vs year-balanced" patterns.
By the numbers
≥ 100
Cutoff
At least 100 distinct dates
MM/DD
Date key
Same MM/DD across years merge
0 - 9 天
Max comp span
SQL inline `nums` enumeration
Data source
competitions cross-joined with an inline nums (0..9) table explodes each N-day comp into N rows (constrained by start_date + n ≤ end_date). Joined to results, every concrete competing date becomes a row. DISTINCT MM/DD merges across years, then GROUP_CONCAT collects.
sql
SELECT COUNT(DISTINCT competition_date) attended_dates,
person_id,
GROUP_CONCAT(DISTINCT competition_date
ORDER BY competition_date ASC SEPARATOR ',') dates_list
FROM (
SELECT person_id, DATE_FORMAT(comp_date, '%m/%d') competition_date
FROM results
JOIN (SELECT id, DATE_ADD(start_date, INTERVAL n DAY) comp_date
FROM competitions
JOIN nums ON DATE_ADD(start_date, INTERVAL n DAY) <= end_date) c
ON c.id = results.competition_id) d
GROUP BY person_id
HAVING attended_dates >= 100Algorithm / pipeline
1
Explode multi-day comps
nums subquery enumerates 0-9, cross-joined to competitions; DATE_ADD(start_date, INTERVAL n DAY) <= end_date trims non-existent days. Multi-day comps expand into multiple rows.2
Date → MM/DD
DATE_FORMAT(.., '%m/%d') strips year — 2020 and 2024 same date merge.3
Per-person COUNT DISTINCT
GROUP BY
person_id, count distinct dates; HAVING >= 100 trims casual cubers.4
TS regroup by month + %
dates_list 01/05,01/12,... is regrouped by month; per-month % = days.length / DAYS_IN_MONTH[m] (Feb at 29 days).Caveats & edges
numscapped at 9 → no support for 11+ day comps (none in WCA, safe).MM/DDdiscards year — same day across years dedup, so "days competed on" is multi-year-merged distinct.- Feb uses 29 days as denominator — non-leap 2/29 rows just don't exist, but % shown is slightly underweighted (1/29) — not corrected.