Brightlane's account management team needs every order annotated with the amount of that customer's most recent order across their entire history. The annotation should reflect the customer's final chronological order, not just whatever order has been processed up to the current row.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's final order chronologically.
Assumptions:
- The
orderstable has one row per order with anid, acustomer_id, atotal_amount, and anordered_attimestamp. - A customer's final order is the order with the largest
ordered_atfor thatcustomer_id. The same final-order amount appears on every row sharing acustomer_id, including rows that fall earlier in the customer's chronological sequence. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andlast_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,
LAST_VALUE(total_amount) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS last_order_amount
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
LAST_VALUE returns the value at the last position of the partition's frame, not the partition itself. With ORDER BY present and no explicit frame, the default frame ends at the current row, which makes LAST_VALUE return the current row's value on every row. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING widens the frame to the entire partition, so LAST_VALUE then returns the customer's true final-order amount on every row.
Clause by clause
SELECT id, customer_id, total_amount,
LAST_VALUE(total_amount) OVER (
PARTITION BY customer_id ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order_amount
FROM orders
ORDER BY customer_id, ordered_at- The window's
PARTITION BY customer_id ORDER BY ordered_atdefines each customer's orders in chronological order. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGextends the frame across the whole partition so the "last" position is the customer's final order, not the current row.LAST_VALUE(total_amount)then returns thetotal_amountat that final position on every row.- The outer
ORDER BY customer_id, ordered_atcontrols the printed sequence, separate from the window's ordering.
Why this and not the same syntax as FIRST_VALUE
FIRST_VALUE returned the right value with no explicit frame in the easies because position 1 of the partition is in every running frame. LAST_VALUE is the symmetric case from the other end. The last position of a frame that stops at the current row is the current row. Without widening the frame, LAST_VALUE looks correct on the very last row of each partition and wrong on every row before it. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is mandatory whenever LAST_VALUE is intended to mean "the partition's last value."
The trap
Omit the explicit frame and LAST_VALUE returns the current row's value on every row, because the default frame's tail is the current row. The query runs without error. The numbers look plausible because they are real values from the customer's history. They are not the customer's final value. Whenever LAST_VALUE is the function being used, the frame must be widened explicitly.
You practiced LAST_VALUE(column) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) — the explicit full-partition frame is required because the default frame ends at the current row, leaving LAST_VALUE returning the current row's value rather than the partition's true last.
