Monthly Purchase Counts — Grouping by Date Periods in SQL

The problem

Scenario: Streamhub's revenue team monitors monthly conversion trends and needs the count of purchase activity rolled up by calendar month.

Task: Write a query to return each calendar month and the number of purchase events recorded in that month.

Assumptions:

The events table holds one row per recorded event, with the timestamp stored in occurred_at and the kind of activity stored in event_type.
A purchase event has event_type equal to 'purchase'.
The result covers only purchase events.

Output:

One row per calendar month that contains at least one purchase event.
Columns in this order: month (the first day of the calendar month), purchase_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
  DATE_TRUNC('month', occurred_at)::date AS MONTH,
  COUNT(*) AS purchase_count
FROM
  events
WHERE
  event_type = 'purchase'
GROUP BY
  DATE_TRUNC('month', occurred_at)

The shape

The WHERE restricts the rows to purchases before any grouping happens, then date_trunc('month', occurred_at) collapses every surviving timestamp to the first day of its month. COUNT(*) runs against the filtered, truncated rows and returns the per-month purchase volume.

Clause by clause

SELECT date_trunc('month', occurred_at)::date AS month, COUNT(*) AS purchase_count returns one row per month with the count of purchase events in it. The ::date cast removes the time component from the truncated timestamp. COUNT(*) counts every row that reached the aggregator, which is every purchase row.
FROM events reads the event log.
WHERE event_type = 'purchase' runs before the grouping. Non-purchase rows are discarded at this stage, so they never contribute to any group's count and a month with only signups or pageviews will not appear in the result at all.
GROUP BY date_trunc('month', occurred_at) repeats the truncation as the grouping key. Every purchase recorded in October 2023 truncates to 2023-10-01 and lands in the same group, producing the count of 2 visible in that month's row.

Why `WHERE` and not a filter inside the aggregate

The filter has to run before the count, because the count is being taken across only the purchase rows. Putting the restriction in WHERE removes non-purchase rows from the picture entirely, which is exactly the intent. The alternative form, a conditional aggregate like COUNT(*) FILTER (WHERE event_type = 'purchase'), would keep non-purchase months in the result with a count of zero, which is not what the prompt asks for: the output covers only months containing at least one purchase.

You practiced applying a category restriction before truncating to month, so the per-month counts reflect only the targeted event type.

Return each calendar month and the number of purchase `events` recorded in that month

The shape

Clause by clause

Why `WHERE` and not a filter inside the aggregate

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

The shape

Clause by clause

Why WHERE and not a filter inside the aggregate

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

Why `WHERE` and not a filter inside the aggregate