SQL to group by week

SELECT DATE_TRUNC('week', created_at) AS week,
       COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week;

DATE_TRUNC('week', ...) snaps each timestamp back to the Monday that starts its ISO week, giving one clean date per week.

SELECT YEARWEEK(created_at, 3) AS week,
       COUNT(*) AS orders
FROM orders
GROUP BY YEARWEEK(created_at, 3)
ORDER BY week;

Mode 3 uses ISO 8601 weeks (Monday start) and folds the year in, so week 1 of 2024 never merges with week 1 of 2025.

SELECT YEAR(created_at) AS year,
       DATEPART(ISO_WEEK, created_at) AS iso_week,
       COUNT(*) AS orders
FROM orders
GROUP BY YEAR(created_at), DATEPART(ISO_WEEK, created_at)
ORDER BY year, iso_week;

DATEPART(ISO_WEEK, ...) gives ISO week numbers; pairing with YEAR() keeps different years apart (see the tip for the early-January edge case).

SELECT strftime('%Y-%W', created_at) AS week,
       COUNT(*) AS orders
FROM orders
GROUP BY strftime('%Y-%W', created_at)
ORDER BY week;

%W counts weeks from the first Monday of the year (00-53).