Monthly Events vs Previous Month — Period-over-Period in SQL

The problem

Scenario: Streamhub's analytics team is reviewing platform engagement trends and wants each month's event volume shown alongside the prior month's volume.

Task: Write a query to return each calendar month, the total number of events recorded in that month, and the total number of events recorded in the immediately preceding calendar month.

Assumptions:

A calendar month is identified by its first day and covers every event recorded within that month.
The earliest month in the data has no preceding month; its prev_month_count value is missing.

Output:

One row per calendar month present in the data.
Columns in this order: month (the first day of the calendar month), event_count, prev_month_count.
Sorted by month ascending.

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 event_count,
  LAG(COUNT(*)) OVER (
    ORDER BY
      DATE_TRUNC('month', occurred_at)
  ) AS prev_month_count
FROM
  events
GROUP BY
  DATE_TRUNC('month', occurred_at)
ORDER BY
  MONTH

The shape

LAG(COUNT(*)) runs after the rows have been grouped into monthly buckets, so it reaches back exactly one bucket — the previous calendar month — and attaches that month's event count next to the current month's count. One query produces both numbers on the same row.

Clause by clause

SELECT DATE_TRUNC('month', occurred_at)::date AS month, COUNT(*) AS event_count, LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', occurred_at)) AS prev_month_count returns the month bucket, the month's event count, and the previous month's count as an adjacent column. LAG with no offset argument reaches back one row in the ordered window.
FROM events reads the event stream; every recorded event is in scope.
GROUP BY DATE_TRUNC('month', occurred_at) produces one row per calendar month so COUNT(*) becomes the monthly total.
ORDER BY month returns the months chronologically.

The trap

The window's ORDER BY and the outer ORDER BY are doing different jobs. The window's ORDER BY DATE_TRUNC('month', occurred_at) defines which row is "one back" inside LAG; the outer ORDER BY month controls the print order of the final result. They happen to use the same key here, but they are independent clauses. Dropping the window's ORDER BY would leave the lookback in an undefined sequence, and the prior-month values would no longer be guaranteed to come from the actual prior month.

You practiced using LAG over monthly event totals to attach each month's prior-month count alongside the current count as an inline column.

Return each calendar month, the total number of `events` recorded in that month, and the total number of `events` recorded in the immediately preceding calendar month

The shape

Clause by clause

The trap

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