Most comps attended in a single week
PersonAlign comps to the ISO week (Monday-first): use WEEKDAY() to back-compute the week's day-0 and day-6, then group by (person, week) with COUNT. 4× tighter than the monthly window.
Top entries at 3 are already brutal — multiple Saturday venues on the same day means tight venue-hopping.
By the numbers
≥ 3
Cutoff
`HAVING attended_within_week >= 3`
周一 - 周日
Week edges
ISO, WEEKDAY=0 = Monday
DISTINCT
Dedup
Multi-event same comp → 1
Data source
results deduped, joined to competitions. DATE_ADD(start_date, INTERVAL -WEEKDAY(start_date) DAY) snaps start_date to that week's Monday — used as the week anchor. Group by (person, week_start, week_end, year).
sql
SELECT COUNT(*) attended_within_week, person_id,
DATE_ADD(start_date, INTERVAL -WEEKDAY(start_date) DAY) week_start_date,
DATE_ADD(start_date, INTERVAL 6-WEEKDAY(start_date) DAY) week_end_date
FROM (SELECT DISTINCT competition_id, person_id FROM results) r
JOIN competitions ON competitions.id = competition_id
GROUP BY person_id, week_start_date, week_end_date, YEAR(start_date)
HAVING attended_within_week >= 3
ORDER BY attended_within_week DESCAlgorithm / pipeline
1
Dedup (person, comp)
Same as the monthly version — multi-event same comp → 1.
2
Anchor to ISO week
WEEKDAY in MySQL: 0 = Monday; subtract back to the week's Monday, add 6 for Sunday. These are the bucket keys.3
Group by (person, week) + COUNT
YEAR(start_date) joins the GROUP BY to guard the unlikely-but-possible cross-year same-week-num collision.4
Pretty-print dates
TS side formats
week_start_date / week_end_date as d Mon YYYY to prevent line wraps.Caveats & edges
- Week boundary is ISO (Mon-first), not US (Sun-first); a weekend two-day comp counts under the week of its start date only.
- Crossing continents over a single weekend is the classic top-rank pattern: NA morning + EU evening + Asia next day = 3.
- The monthly version can miss cross-month consecutive weeks; this one catches them — and the same 3 in a week may straddle two months.