Grouping by Time Parts

Concepts covered: Date Parts

A monthly revenue report is a GROUP BY on date parts. The grouping has to include every larger part as well: month alone puts March 2023 and March 2024 in one group, so group on year and month together.

Month only

Two Marches fold into 1 row: 5 orders and 200 of revenue from 2 different years.

SELECT
  EXTRACT(MONTH FROM ordered_at) AS order_month,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM ordered_at)
ORDER BY order_month

Year + month

With the year in the key, March 2023 shows 2 orders and 100, and March 2024 shows 3 orders and 100.

SELECT
  EXTRACT(YEAR FROM ordered_at) AS order_year,
  EXTRACT(MONTH FROM ordered_at) AS order_month,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM ordered_at), EXTRACT(MONTH FROM ordered_at)
ORDER BY order_year, order_month

Watch the groups fold

Quarters work the same way through quarter(). In the animation, 4 orders from 2024 fold into 2 groups keyed on year and quarter. COUNT and SUM then run once per group.

SELECT
  year(ordered_at) AS order_year,
  quarter(ordered_at) AS order_quarter,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM orders
GROUP BY year(ordered_at), quarter(ordered_at)
ORDER BY order_year, order_quarter

quarter() knows only calendar quarters. A fiscal year that starts in February needs its own mapping: quarter(date_add('month', -1, ordered_at)) shifts every date back 1 month before reading the quarter, so February to April becomes quarter 1.

Weeks belong to their own year

Weekly reports group on the ISO week, week(). An ISO week runs Monday to Sunday. Week 1 is the week holding the year's first Thursday, so the last days of December can sit in week 1 of the next year. Pair week() with year_of_week(), the year that owns the week, and never with year().

With year() and week() as the key, the last 2 days of 2024 read as 2024 week 1. January 1 to 7, 2024 read the same way, so 2 weeks 12 months apart merge into 1 row.

An orders table spans 2023 and 2024. Which GROUP BY gives 1 row per ISO week, with no week merged across years?

• GROUP BY year(ordered_at), week(ordered_at)
• GROUP BY year_of_week(ordered_at), week(ordered_at)
• GROUP BY week(ordered_at)
• GROUP BY year_of_week(ordered_at), day_of_week(ordered_at)

One date column that sorts and charts

Grouping on 2 number columns per period leaves output that is clumsy to sort and to chart, and a single date groups better. date_add('day', 1 - day(d), d) moves a date back to the 1st of its month, and date_add('day', 1 - day_of_week(d), d) moves it back to its Monday. Grouped on that date, each row is labelled with a real period start that sorts and charts as a date. The CTE casts each timestamp to DATE once so the grouping expression stays short.

Week start

Each date moves back to its Monday, so 3 orders from February 26 to March 3 share the week of February 26.

WITH dated AS (
  SELECT
    CAST(ordered_at AS DATE) AS order_date,
    amount
  FROM orders
)
SELECT
  date_add('day', 1 - day_of_week(order_date), order_date) AS week_start,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM dated
GROUP BY date_add('day', 1 - day_of_week(order_date), order_date)
ORDER BY week_start

Month start

Each date moves back to the 1st, so the 4 March orders share March 1 and the February order stands alone.

WITH dated AS (
  SELECT
    CAST(ordered_at AS DATE) AS order_date,
    amount
  FROM orders
)
SELECT
  date_add('day', 1 - day(order_date), order_date) AS month_start,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM dated
GROUP BY date_add('day', 1 - day(order_date), order_date)
ORDER BY month_start

The week of February 26 holds orders from both February and March, which is why weekly and monthly totals never line up row for row.

Do

• Group on year and month together, or on a rebuilt month-start date
• Pair week() with year_of_week()
• Convert to the reporting zone before taking parts
• Order the output by the same parts you group on

Avoid

• Group on month() alone when the data spans more than 1 year
• Pair week() with year(), which splits weeks at New Year
• Read a missing month as a month of zero revenue
• Compare a partial current month with complete ones

This one combines the lesson. app_events stores event_ms as epoch milliseconds. Count events per calendar month on New York clocks, with 1 row per year and month, in time order.

About this lesson

This section is part of the Dates: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.