0Pricing
SQL Interview Prep · Lesson

Truncating and Bucketing Dates

Grouping by week, month, and quarter with DATE_TRUNC and equivalents.

Why Bucketing Dates Is Asked

"Show revenue by week" or "active users by month" is the bread and butter of analyst interviews. The skill being tested is collapsing precise timestamps into a coarser bucket so rows group together.

The mistake juniors make is extracting just the month number, which merges the same month across different years. The professional answer is truncation: map every timestamp to the start of its period.

  • Week, month, quarter, year buckets
  • DATE_TRUNC and dialect equivalents
  • Grouping correctly so charts line up

DATE_TRUNC: The Core Tool

In PostgreSQL, DATE_TRUNC(unit, ts) zeroes out everything finer than the unit. Truncating to 'month' turns any March timestamp into 2024-03-01 00:00:00.

The return value is still a timestamp, so it sorts chronologically and groups perfectly. This is the single most useful date function for reporting.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

All lessons in this course

  1. Date Arithmetic and Intervals
  2. Truncating and Bucketing Dates
  3. Parsing and Formatting Strings
  4. Time Zones and Timestamps
← Back to SQL Interview Prep