Brightlane's account management team reviews each customer's order history with the largest orders surfaced first for quick scanning. The previous-order amount on each row must reflect chronological sequence — the order placed immediately before this one in time, not the row that happens to appear above it in the display.
Write a query to return every order's ID, customer ID, order amount, and that same customer's chronologically previous order amount. Sort the final result by customer_id ascending, then by total_amount descending within each customer.
Assumptions:
- The previous-order amount is determined by
ordered_atwithin each customer's orders — the order with the largestordered_atstrictly before the current row'sordered_at. - The final display sort has no effect on which order each row's previous-order amount comes from. The chronological lookup happens independently of the display order.
- For a customer's earliest order chronologically — where no prior order is on record — the previous-amount value is missing.
- The final result is sorted by
customer_idascending, then bytotal_amountdescending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andprev_order_amount. Sorted bycustomer_id, thentotal_amountdescending.
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_order_amount
FROM
orders
ORDER BY
customer_id,
total_amount DESCThe shape
The window's ORDER BY ordered_at is what defines "previous order" for the LAG lookup; the query's outer ORDER BY customer_id, total_amount DESC only controls how the rows print. The two clauses run independently. The displayed order is dollar-sorted; the chronological lookup still happens against the time-sorted partition underneath.
Clause by clause
SELECT id, customer_id, total_amount, LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_order_amountreturns each order's identifying columns and the chronologically-previous order amount. The window'sORDER BY ordered_atis the sort that determines which row is "previous"; this is fully resolved inside the window function, before the outer query touches the rows.FROM ordersreads every order.ORDER BY customer_id, total_amount DESCsorts the printed result by customer, then by dollar amount descending. This sort runs after the window function has already produced theprev_order_amountvalues. It changes the display order, not the values.
Why the two ORDER BY clauses do not conflict
SQL evaluates window functions before the outer ORDER BY. By the time the outer sort runs, every row already carries its prev_order_amount, computed against the partition's chronological sequence. Reordering the rows after the fact does not re-run the lookup. Each row's prev_order_amount stays attached to that row no matter where it ends up in the printed output. The window's ORDER BY and the outer ORDER BY are different sorts at different stages of evaluation.
The trap
Removing or changing the window's ORDER BY clause would change the LAG lookup; removing the outer ORDER BY would not. The two clauses look identical in syntax and are not interchangeable. A reader who tries to "simplify" by collapsing them into one will either break the lookup (if they move it to the outer query) or scramble the display (if they remove the inner one). The two have to coexist because they answer two different questions: in what order does the lookup walk the rows, and in what order does the result print.
You practiced separating the window's ORDER BY (by ordered_at, defining 'previous') from the final query's ORDER BY (by total_amount descending, defining display order) — two independent sort instructions that don't bleed into each other.
