Group by Year and Month — Grouping by Date Periods in SQL

The problem

Scenario: Streamhub's data team is producing a report where year and month-of-year are reported as two separate numeric columns rather than a single date, so analysts can slice by either dimension independently.

Task: 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.

Assumptions:

The year value is the four-digit calendar year of the event.
The month_num value is the calendar month of the event, expressed as a number from 1 (January) through 12 (December).
One result row covers every event whose calendar year and calendar month match.

Output:

One row per (year, month_num) combination present in the data.
Columns in this order: year, month_num, event_count.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  EXTRACT(
    YEAR
    FROM
      occurred_at
  ) AS YEAR,
  EXTRACT(
    MONTH
    FROM
      occurred_at
  ) AS month_num,
  COUNT(*) AS event_count
FROM
  events
GROUP BY
  EXTRACT(
    YEAR
    FROM
      occurred_at
  ),
  EXTRACT(
    MONTH
    FROM
      occurred_at
  )

The shape

EXTRACT pulls the year and the month-of-year out of occurred_at as two independent numeric values, and the grouping happens on the pair. The query reports time as two columns of numbers, not as a single truncated date, so analysts can slice on either dimension on its own.

Clause by clause

SELECT EXTRACT(year FROM occurred_at) AS year, EXTRACT(month FROM occurred_at) AS month_num, COUNT(*) AS event_count returns one row per (year, month) pair. Each EXTRACT returns a number, not a date, so the report shows 2023 and 12 instead of a December 2023 date. COUNT(*) counts the events that fall inside the pair.
FROM events reads every event.
GROUP BY EXTRACT(year FROM occurred_at), EXTRACT(month FROM occurred_at) uses the same two expressions as the grouping keys. The grouping is on the pair, not on each value independently, so December 2022 and December 2023 are kept apart because their year values differ even though their month_num matches.

Why `EXTRACT` and not `date_trunc`

date_trunc('month', occurred_at) would return a single timestamp per month, which is the right shape for time-series charts but the wrong shape here. The prompt asks for year and month-of-year as two separate numeric columns. EXTRACT returns a number for each part, which is exactly that shape. The two functions live in the same node for this reason: date_trunc is for grouping along a continuous calendar axis, and EXTRACT is for pulling calendar parts out as numbers that analysts can pivot on independently.

You practiced extracting calendar parts as separate numeric values so year and month-of-year can be reported as independent dimensions instead of a single truncated date.

Return the year, the month number from 1 through 12, and the total number of `events` for each year-month combination

The shape

Clause by clause

Why `EXTRACT` and not `date_trunc`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why EXTRACT and not date_trunc

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why `EXTRACT` and not `date_trunc`