Brightlane's account management team is reviewing delivered order sequences only — pending and cancelled orders are excluded from this analysis.
Write a query to return every delivered order's ID, customer ID, amount, and that same customer's immediately preceding delivered-order amount.
Assumptions:
- A delivered order has
status = 'delivered'. Only delivered orders should appear in the result, and only delivered orders should contribute to the previous-amount lookup. - A customer's previous delivered order is the delivered order with the largest
ordered_atstrictly before the current row'sordered_at, restricted to that customer. - For a customer's first delivered order — where no prior delivered order is on record — the previous-amount value is missing.
- The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per delivered order, with columns
id,customer_id,total_amount, andprev_delivered_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) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at
) AS prev_delivered_amount
FROM
orders
WHERE
status = 'delivered'
ORDER BY
customer_id,
ordered_atThe shape
WHERE runs before any window function, so the LAG lookup operates only on the rows that survived the filter. The "previous delivered order" on each row is the previous row inside the filtered population, not the previous order that happened to exist in the raw table.
Clause by clause
SELECT id, customer_id, total_amount, LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_delivered_amountreturns each delivered order's identifying columns and the dollar amount of that customer's previous delivered order. The window definition is the same partitioned-and-ordered shape as the unfiltered version.FROM ordersreads every order.WHERE status = 'delivered'filters to delivered orders before the window function runs. Pending and cancelled orders are dropped from the table the window sees, so the chronological sequence inside each partition contains only delivered orders.ORDER BY customer_id, ordered_atsorts the result chronologically within each customer.
The trap
WHERE and window functions interact in a specific order that is easy to misread. The filter applies first, then the window operates on what remains. That means prev_delivered_amount is the most-recent earlier delivered order, not the immediately-preceding order regardless of status. If a customer's order history reads delivered, cancelled, delivered, the second delivered row sees the first delivered row as its predecessor, with the cancelled order invisible to the lookup. This is usually what the consumer wants when they filter; it is the whole point of filtering. But a reader expecting "the order placed immediately before this one in real time" will read the column wrong on customers whose history mixes statuses.
You practiced LAG over a pre-restricted record set — the WHERE runs first, so the window function sees only the surviving records when computing offsets.
