Grouping by Date Periods in SQL
Grouping by date periods means truncating timestamps or dates to a calendar unit and using the truncated value as the GROUP BY key. The result is one row per period (one per day, one per week, one per month) with aggregated measures across all rows that fall within each period.
Before this Date Truncation and Extraction, GROUP BY
Builds toward Running Totals and Cumulative Metrics
Grouping by date period means truncating timestamps to a calendar unit — month, week, day — and using the truncated value as the GROUP BY key. The result is one row per period with aggregated measures across all rows that fall within it.
Raw timestamps are almost never the right grouping key. An orders table with a created_at timestamp down to the millisecond has a unique timestamp for nearly every row. Grouping by the raw value produces one group per row — not aggregation at all. The useful question is "what happened in March?" not "what happened at 14:32:07.443 on March 3rd?"
date_trunc() reduces a timestamp to the start of the specified period. All timestamps in the same period truncate to the same value and group correctly.
SELECT
date_trunc('month', ordered_at)::date AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY monthThis produces one row per month with a count and revenue total. Every order in March 2024 truncates to 2024-03-01 00:00:00 and ends up in the same group.
The one thing that trips people up
The GROUP BY expression must match the SELECT expression exactly, or reference it by position. Using the alias defined in SELECT — GROUP BY month — fails in PostgreSQL: aliases aren't available at the GROUP BY evaluation stage.
This works:
GROUP BY date_trunc('month', ordered_at)So does this:
GROUP BY 1This fails:
GROUP BY month -- alias not visible hereThe output of date_trunc() is a timestamp, even when the input is a date. For display or downstream joins that expect a date type, cast after truncating:
date_trunc('month', ordered_at)::date AS monthWeek truncation starts on Monday
date_trunc('week', ...) follows ISO week convention: weeks begin on Monday. If your reporting context expects Sunday-starting weeks, the truncation will group differently than expected. For most analytical work, aligning to ISO weeks is the practical choice. If you need Sunday weeks, that requires a workaround with date arithmetic.
Grouping by date period is the foundation for almost all time-series work. Period aggregates feed into running totals, period-over-period comparisons, and trend analysis — all of which need clean one-row-per-period output to start from.
Available precision values
date_trunc() supports: 'microseconds', 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century'. You'll use 'day', 'week', 'month', 'quarter', and 'year' most often. 'quarter' truncates to the first day of the quarter (January 1, April 1, July 1, October 1), which is useful for quarterly reporting without manual CASE WHEN logic.
Multiple grouping levels in one query
You can compute period aggregates at different levels in the same query using CTEs. Compute daily totals in one CTE, then aggregate those to monthly in the next. This is cleaner than re-aggregating the raw table at two levels in the same GROUP BY, and it gives each layer a clear, readable name.
10 Grouping by Date Periods practice problems
Write a query to return each calendar month, the number of `orders` placed in that month, and the total `orders` revenue for that month.
Write a query to return each calendar day and the total number of `events` recorded on that day.
Write a query to return each calendar week and the number of `orders` placed in that week.
Write a query to return each calendar quarter, the number of `orders` placed in that quarter, the total `orders` revenue, and the average value per order.
Write a query to return each calendar month and the number of purchase `events` recorded in that month.
Write a query to return the year, the month number from 1 through 12, and the total number of `events` for each year-month combination.
Write a query to return each calendar year, the number of `orders` placed in that year, the total `orders` revenue, and the average value per order.
Write a query to return each month-of-year and the total number of `events` recorded in that month-of-year across every year in the data.
Write a query to return each calendar day in January 2024 on which at least one order was placed, the number of `orders` placed on that day, and the total `orders` revenue.
Write a query to return the year, the quarter number from 1 through 4, the number of `orders` placed in that year-quarter, and the total `orders` revenue for that year-quarter.
These problems are part of the Grouping by Date Periods lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.