Scenario: Brightlane's customer success team is monitoring how each individual customer's monthly spending shifts from one month to the next.
Task: Write a query to return each customer's customer_id, calendar month, total spend in that month, and total spend in the immediately preceding month within that same customer's own order history.
Assumptions:
- A customer is present in a month only if they have at least one order placed in that month.
- Each customer's
prev_month_spendis drawn solely from that same customer's earlier order history. - The earliest month in each customer's own history has no preceding month within that customer; its
prev_month_spendvalue is missing.
Output:
- One row per (
customer_id,month) pair present in the data. - Columns in this order:
customer_id,month(the first day of the calendar month),monthly_spend,prev_month_spend. - Sorted by
customer_idascending, thenmonthascending.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
customer_id,
DATE_TRUNC('month', ordered_at)::date AS MONTH,
SUM(total_amount) AS monthly_spend,
LAG(SUM(total_amount)) OVER (
PARTITION BY
customer_id
ORDER BY
DATE_TRUNC('month', ordered_at)
) AS prev_month_spend
FROM
orders
GROUP BY
customer_id,
DATE_TRUNC('month', ordered_at)
ORDER BY
customer_id,
MONTHThe shape
Without PARTITION BY, LAG would walk across customer boundaries and pair one customer's first month with a different customer's last month. PARTITION BY customer_id isolates each customer's history into its own window, so the lookback stays inside the customer it belongs to.
Clause by clause
SELECT customer_id, DATE_TRUNC('month', ordered_at)::date AS month, SUM(total_amount) AS monthly_spend, LAG(SUM(total_amount)) OVER (PARTITION BY customer_id ORDER BY DATE_TRUNC('month', ordered_at)) AS prev_month_spendreturns each customer's month bucket, that month's total spend, and the same customer's prior-month spend.PARTITION BY customer_idis what guarantees the lookback never leaks across customers;ORDER BY DATE_TRUNC('month', ordered_at)sets the chronological sequence inside each customer's partition.FROM ordersreads every order in the table.GROUP BY customer_id, DATE_TRUNC('month', ordered_at)collapses the orders to one row per (customer, month) pair, which is the grain the window operates on.ORDER BY customer_id, monthprints each customer's history as a contiguous block, top to bottom in time order.
The trap
The window's PARTITION BY and ORDER BY are separate from the outer GROUP BY and outer ORDER BY, even though they reference some of the same columns. The window does its lookback on the already-grouped rows, partitioned and ordered as the OVER clause specifies. Dropping PARTITION BY customer_id from the window does not raise an error — it silently produces a query where the first row of customer B pulls customer A's last month as its "prior" value. The output looks normal, every number is a real number, and the report is wrong in a way no one will notice from the result alone.
You practiced partitioning LAG by customer_id so each customer's prior-month spend is drawn from their own history, never from another customer's row.
