Weekly Orders vs Previous Week — Period-over-Period in SQL

The problem

Scenario: Brightlane's operations team monitors weekly order flow to catch sudden drops or spikes early.

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

Assumptions:

A calendar week is identified by its starting Monday and covers every order placed within that week.
The earliest week in the data has no preceding week; its prev_week_count value is missing.

Output:

One row per calendar week present in the data.
Columns in this order: week_start (the first day of the calendar week, a Monday), order_count, prev_week_count.
Sorted by week_start 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('week', ordered_at)::date AS week_start,
  COUNT(*) AS order_count,
  LAG(COUNT(*)) OVER (
    ORDER BY
      DATE_TRUNC('week', ordered_at)
  ) AS prev_week_count
FROM
  orders
GROUP BY
  DATE_TRUNC('week', ordered_at)
ORDER BY
  week_start

The shape

The periodicity is set by the truncation, not by LAG. DATE_TRUNC('week', ordered_at) bins each order into its starting-Monday week, and LAG reaches back one row in that weekly sequence. Moving the analysis from months to weeks is a one-word change to the truncation argument.

Clause by clause

SELECT DATE_TRUNC('week', ordered_at)::date AS week_start, COUNT(*) AS order_count, LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('week', ordered_at)) AS prev_week_count returns the week's starting Monday, that week's order count, and the immediately preceding week's count. LAG with no explicit offset reaches back one row in the ordered sequence — one row at weekly grain is one week.
FROM orders reads every order.
GROUP BY DATE_TRUNC('week', ordered_at) collapses the orders to one row per calendar week.
ORDER BY week_start returns the weeks chronologically.

The trap

LAG's offset is a row count, not a time unit. At weekly grain, LAG(COUNT(*)) looks one row back, which is one week back. The same call at monthly grain looks one row back, which is one month back. The offset never "knows" what unit the grain is in — that is decided entirely by the truncation. Mixing the two assumptions is the standard period-over-period mistake: reaching for a hard-coded offset of 4 to get a month from weekly data ignores the fact that months are not uniformly four weeks long, and the result drifts. The right way to change periodicity is to change the truncation, not the offset.

You practiced using LAG over weekly order totals so each week's prior-week value is attached as an inline column at a non-monthly periodicity.

Return each calendar week, the number of `orders` placed in that week, and the number of `orders` placed in the immediately preceding week

The shape

Clause by clause

The trap

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