Brightlane's finance team is building a calculation that needs a numeric prior-order value on every row, including a customer's first purchase. Where no prior order is on record, the team wants 0.00 rather than missing so downstream arithmetic continues uninterrupted.
Write a query to return every order's ID, customer ID, order amount, and that customer's previous order amount.
Assumptions:
- A customer's previous order is the order with the largest
ordered_atstrictly before the current row'sordered_at, restricted to that customer. - For a customer's first order — where no prior order is on record — the previous-amount column should hold
0.00instead of a missing value. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andprev_order_amount. Sorted bycustomer_id, thenordered_at.
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
id,
customer_id,
total_amount,
LAG(total_amount, 1, 0.00) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at
) AS prev_order_amount
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
The third argument to LAG supplies a default value for rows that have no prior row to look back to. Writing LAG(total_amount, 1, 0.00) says "reach back one row; if that row does not exist, return 0.00 instead of NULL." The finance team's arithmetic gets a real number on every row, including each customer's first order.
Clause by clause
SELECT id, customer_id, total_amount, LAG(total_amount, 1, 0.00) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_order_amountreturns the order's identifying columns and a guaranteed-numeric prior-order amount. The middle argument1is the offset (look back one row); the third argument0.00is the default that fires when the offset reaches outside the partition. The window definition is the same as the un-defaulted form: partition per customer, order chronologically.FROM ordersreads every order.ORDER BY customer_id, ordered_atsorts the result for chronological reading.
Why the third argument and not a separate substitution step
LAG's three-argument signature folds the substitution into the window function itself, so the result type is consistent and no downstream step has to handle NULL. The default value is type-checked against total_amount at parse time, so LAG(total_amount, 1, 0.00) produces a numeric on every row. Reaching for a separate NULL-substitution function would not be wrong, but it would be a second step where the window function already offers a one-step solution that is purpose-built for exactly this case.
The trap
0.00 is a presentational sentinel, not a real prior-order amount. Any downstream calculation that aggregates prev_order_amount or treats it as a true previous purchase will count those zeros and skew the result. The right place for the default is in a report where the consumer needs a non-NULL value for display or for arithmetic that propagates NULL too aggressively. The wrong place is anywhere a SUM(prev_order_amount) or an average gets computed on the column; there, the default silently distorts the answer.
You practiced LAG(column, 1, default) — the third argument supplies a fallback when the offset reaches outside the partition, making the wrapper around LAG unnecessary.
