Monthly Orders vs Next Month — Period-over-Period in SQL

The problem

Scenario: Brightlane's growth team is planning headcount for the coming month and wants each month's order count shown alongside the count from the following month.

Task: Write a query to return each calendar month, the number of orders placed in that month, and the number of orders placed in the immediately following calendar month.

Assumptions:

A calendar month is identified by its first day and covers every order placed within that month.
The latest month in the data has no following month; its next_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), order_count, next_month_count.
Sorted by month ascending.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

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', ordered_at)::date AS MONTH,
  COUNT(*) AS order_count,
  LEAD(COUNT(*)) OVER (
    ORDER BY
      DATE_TRUNC('month', ordered_at)
  ) AS next_month_count
FROM
  orders
GROUP BY
  DATE_TRUNC('month', ordered_at)
ORDER BY
  MONTH

The shape

LEAD is the forward-looking sibling of LAG: it reaches one row ahead in the ordered sequence instead of one row back. Each month's row carries the count from the month that follows it, which is exactly the look-ahead the headcount plan needs.

Clause by clause

SELECT DATE_TRUNC('month', ordered_at)::date AS month, COUNT(*) AS order_count, LEAD(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', ordered_at)) AS next_month_count returns the month bucket, the month's order count, and the following month's order count. COUNT(*) counts every row in the group; LEAD with no explicit offset advances exactly one row in the ordered sequence.
FROM orders reads every order in the table.
GROUP BY DATE_TRUNC('month', ordered_at) collapses the orders into one row per calendar month so COUNT(*) produces the monthly total.
ORDER BY month prints the months in calendar order so the next-month column lines up with the row it describes.

Why `LEAD` and not `LAG`

LAG would have the prior month sitting next to each current month, which is the wrong direction for a forward-looking report. LEAD reaches in the same direction the planner is reading: this month is here, and the count next month is the value to the right. Switching to LAG would silently produce the wrong column for the question being asked, with no error to flag the mistake.

The trap

The last month in the data has no following row in the window, so next_month_count is NULL for that final row. That NULL is honest: there is no measurement for the month after the last one. It is not a candidate for a default substitution unless the report's downstream consumer specifically needs a number instead of a missing value.

You practiced using LEAD over monthly order totals to attach each month's following-month value alongside the current value as an inline column.

Return each calendar month, the number of `orders` placed in that month, and the number of `orders` placed in the immediately following calendar month

The shape

Clause by clause

Why `LEAD` and not `LAG`

The trap

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

The shape

Clause by clause

Why LEAD and not LAG

The trap

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

Why `LEAD` and not `LAG`