Relative Date Buckets — Solution & ExplanationProblems / Relative Date Buckets / EditorialApproach

One distance, many buckets. DATE_DIFF('day', order_date, <reference>) converts every row into a plain integer distance; the CASE then works on numbers, not dates.

Top-down thresholds. <= 6, then <= 29, else older — each branch implicitly excludes what earlier branches took, so no BETWEEN chains.

Count per label and order (these labels sort usefully as strings; production dashboards usually add a numeric sort key).

Key InsightAn N-day window contains days 0 through N-1 — "last 7 days" ends at days_ago = 6, not 7. Off-by-one bucket edges are the classic silent dashboard bug: nothing errors, one day of orders just lands in the wrong bucket forever. Anchoring to a *fixed* reference date (rather than CURRENT_DATE) is the other discipline this problem models: deterministic queries are testable queries.Hints

Everything is relative to one fixed anchor — compute days_ago once (order_date to the reference) and bucket that number, rather than writing date arithmetic in every branch condition.

Descending CASE thresholds keep the ranges exclusive without BETWEEN: <= 6 catches the last week, the next branch's <= 29 only sees what the first branch let through.

Check the boundaries: exactly 6 days ago is still last_7_days (a 7-day window includes day 0), exactly 29 is still last_30_days, exactly 30 is older. The data sits orders right on each edge.

Solve Relative Date Buckets yourself →