Brightlane's growth team tracks each customer's third purchase as a spending milestone. Every order should be annotated with the amount of that customer's third order — and where a customer has fewer than three orders on record, the third-order amount should be 0.00 rather than missing.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's third order chronologically.
Assumptions:
- A customer's third order is the order with the third-smallest
ordered_atfor thatcustomer_id, where position counting starts at1. The same third-order amount appears on every row sharing acustomer_id. - For customers who have placed fewer than three orders, the third-order amount column carries
0.00on every row for that customer instead 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, andthird_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,
COALESCE(
NTH_VALUE(total_amount, 3) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
),
0.00
) AS third_order_amount
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
NTH_VALUE(total_amount, 3) returns the value of total_amount at position 3 of the partition, and the COALESCE substitutes 0.00 whenever that position does not exist. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame lets every row in the partition see position 3, not only the rows from position 3 onward.
Clause by clause
SELECT id, customer_id, total_amount,
COALESCE(
NTH_VALUE(total_amount, 3) OVER (
PARTITION BY customer_id ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
0.00
) AS third_order_amount
FROM orders
ORDER BY customer_id, ordered_at- The window partitions each customer's orders, chronologically ordered, with a full-partition frame so position 3 is visible from row 1 onward.
NTH_VALUE(total_amount, 3)returns thetotal_amountat position 3, or NULL if the partition has fewer than three rows.COALESCE(..., 0.00)replaces that NULL with0.00when a customer has fewer than three orders.- The outer
ORDER BYcontrols display order.
Why COALESCE and not an NTH_VALUE default argument
LAG and LEAD accept a third argument that supplies a default when no row exists at the requested offset. NTH_VALUE does not. The function signature is NTH_VALUE(expression, n) only, with no slot for a fallback. The substitution has to happen outside the function call, which is what COALESCE is for. COALESCE(NTH_VALUE(...), 0.00) reads each row's NTH_VALUE result and returns 0.00 when that result is NULL.
The trap
The frame matters and the COALESCE matters, and they handle different problems. Without the explicit UNBOUNDED FOLLOWING frame, NTH_VALUE(..., 3) returns NULL on the first two rows of every partition because position 3 is not in the running frame yet, even for customers with five or six orders. Without the COALESCE, the function returns NULL for every row of a customer with only one or two orders. The two protections are not interchangeable. The frame widens what NTH_VALUE can see; the COALESCE substitutes a value when there is genuinely nothing to see. A query missing the frame returns NULL on rows that should have a value; a query missing the COALESCE returns NULL on rows where the prompt wants 0.00. Both protections are needed.
You practiced wrapping NTH_VALUE in COALESCE — when a partition is shorter than the requested position, NTH_VALUE returns missing; the COALESCE substitutes a domain-appropriate fallback so downstream arithmetic stays unbroken.
