Brightlane's customer analytics team needs every order annotated with both ends of that customer's purchase history — the amount of the customer's very first order and the amount of the customer's most recent order, attached to every row.
Write a query to return every order's ID, customer ID, order amount, the amount of that same customer's first order chronologically, and the amount of that same customer's most recent order chronologically.
Assumptions:
- A customer's first order is the order with the smallest
ordered_atfor thatcustomer_id. A customer's most recent order is the order with the largestordered_atfor thatcustomer_id. - Both anchor values appear identically on every row sharing a
customer_id, including rows that fall in the middle of 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,first_order_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,
FIRST_VALUE(total_amount) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at
) AS first_order_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
Two window functions partitioned identically by customer_id and ordered by ordered_at produce two anchor annotations on the same row. FIRST_VALUE works with the default frame because position 1 is in every running frame. LAST_VALUE does not, and needs an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame to reach the partition's true last position.
Clause by clause
SELECT id, customer_id, total_amount,
FIRST_VALUE(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS first_order_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
FIRST_VALUEwindow has no explicit frame. The default frame ends at the current row, but position 1 of the partition is inside that frame on every row from row 1 onward, so the default works. - The
LAST_VALUEwindow addsROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGto extend the frame across the whole partition. Without it,LAST_VALUEwould return the current row's value on every row, because the default frame's tail is the current row. - Both functions partition by
customer_idand order byordered_at, so they walk each customer's history in the same chronological sequence. - The outer
ORDER BY customer_id, ordered_atsorts the printed result; this is independent of either window's ordering.
Why two separate OVER clauses and not one shared window
The two functions need different window definitions because they need different frames. FIRST_VALUE is correct under the default frame; LAST_VALUE requires the explicit full-partition frame. Writing the two OVER clauses independently makes each function's frame visible in the SQL. Forcing the two functions to share a WINDOW alias would require either widening FIRST_VALUE's frame unnecessarily or breaking LAST_VALUE by leaving the frame default.
The trap
The two windows look almost identical on the page and behave almost identically in the partition: same PARTITION BY, same ORDER BY. The frame difference is small in text and decisive in result. Copy the FIRST_VALUE window verbatim to LAST_VALUE and every row's last_order_amount will equal its own total_amount, which looks plausible for the last row of each customer and is wrong for every other row. Whenever a single query uses both FIRST_VALUE and LAST_VALUE, the rule is fixed: FIRST_VALUE may use the default frame; LAST_VALUE must use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
You practiced FIRST_VALUE and LAST_VALUE over the same partition — FIRST_VALUE works correctly with the default frame; LAST_VALUE requires an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame so the lookup spans the whole partition rather than ending at the current row.
