Recap — Date Bucketing Recipes

# Date Bucketing Recipes `DATE_TRUNC('month', order_date)` covers maybe 70% of bucketing needs. The other 30% need patterns that aren't built-in. ## 1) Standard buckets (built-in) ```sql DATE_TRUNC('day', order_date) DATE_TRUNC('week', order_date) -- starts on Monday in PostgreSQL DATE_TRUNC('month', order_date) DATE_TRUNC('quarter', order_date) DATE_TRUNC('year', order_date) ``` Easy. Move on. ## 2) Fiscal year that doesn't start in January Many companies run April → March (India), July → June (Australia), or October → September (US federal). Shift the date before truncating: ```sql SELECT DATE_TRUNC('year', order_date - INTERVAL '3 months') + INTERVAL '3 months' AS fiscal_year_start FROM orders; ``` For an April fiscal year: subtract 3 months, truncate, add 3 back. A March 31 date shifts to Dec 31 (truncates to Jan 1 of that calendar year), then adds 3 months → April 1 of that calendar year. A April 1 date shifts to Jan 1 (truncates to Jan 1), adds 3 → April 1. Adapt the `INTERVAL '3 months'` to whatever shift your fiscal needs. ## 3) ISO weeks — week starts Monday, weeks span year boundaries ```sql SELECT EXTRACT(ISOYEAR FROM order_date) AS iso_year, EXTRACT(WEEK FROM order_date) AS iso_week FROM orders; ``` ISO weeks are different from calendar weeks: week 1 is the week containing the first Thursday of the year. Jan 1 might be in week 52 of the previous year. ## 4) Rolling N-day buckets (not aligned to calendar) "Show me last-7-day metrics regardless of weekday": ```sql SELECT order_date, SUM(daily_revenue) OVER ( ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_revenue FROM daily_metrics; ``` Use `ROWS BETWEEN` for fixed-row counts; `RANGE BETWEEN INTERVAL '6 days' PRECEDING` for date-aware sliding (handles missing dates correctly). ## 5) Custom buckets — quarters that aren't quarters "Bucket by 13-week periods starting Jan 6": ```sql SELECT FLOOR(EXTRACT(DAY FROM (order_date - DATE '2026-01-06')) / 91) AS period_id, order_date FROM orders WHERE order_date >= DATE '2026-01-06'; ``` `91` is days per 13-week period. The `FLOOR(... / 91)` gives a stable integer per period. ## 6) Bucketing by relative date — "days since signup" ```sql SELECT EXTRACT(DAY FROM (event_time - signup_time)) AS days_since_signup, COUNT(*) AS event_count FROM user_events GROUP BY 1 ORDER BY 1; ``` This is *days since some user-specific event*, not calendar days. Useful for cohort retention, onboarding funnels. ## 7) "By week, but I want the week to start on Sunday" In PostgreSQL, `DATE_TRUNC('week', ...)` starts Monday. To start Sunday: ```sql SELECT DATE_TRUNC('week', order_date + INTERVAL '1 day') - INTERVAL '1 day' AS week_start; ``` Shift forward, truncate, shift back. Same trick as fiscal year. ## Common interview asks 1. *"Bucket orders into 5-minute windows"* → `DATE_TRUNC('minute', order_time) - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM order_time)::int % 5)`. 2. *"Order weeks Sun-Sat"* → shift trick above. 3. *"Compute day-of-week revenue"* → `EXTRACT(DOW FROM order_date)` then GROUP BY. 4. *"Show only business days"* → `WHERE EXTRACT(DOW FROM order_date) BETWEEN 1 AND 5`. ## Takeaway DATE_TRUNC is your 70% case. The other 30% — fiscal years, ISO weeks, custom buckets, business-day filters — all use shift-truncate-shift, EXTRACT, or arithmetic on `EXTRACT(DAY FROM ...)`. Memorize the shift trick; it solves more than you'd expect.