Brightlane's sales team tracks each customer's second purchase as a key milestone in the customer journey. Every order should be annotated with the amount of that customer's second order.
Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's second order chronologically.
Assumptions:
- A customer's second order is the order with the second-smallest
ordered_atfor thatcustomer_id, where position counting starts at1. The same second-order amount appears on every row sharing acustomer_id. - For customers who have placed only one order — where no second order is on record — the second-order amount is missing on every row for that customer.
- The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andsecond_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,
NTH_VALUE(total_amount, 2) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS second_order_amount
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
NTH_VALUE(total_amount, 2) returns the value of total_amount at position 2 of the partition's frame. With PARTITION BY customer_id ORDER BY ordered_at, position 2 is the customer's second-earliest order. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame is what lets every row in the partition see position 2, including the row that is position 1.
Clause by clause
SELECT id, customer_id, total_amount,
NTH_VALUE(total_amount, 2) OVER (
PARTITION BY customer_id ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_order_amount
FROM orders
ORDER BY customer_id, ordered_at- The window partitions each customer's orders, chronologically ordered.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGwidens the frame to the whole partition so position 2 is in scope from row 1 onward, not only from row 2 onward.NTH_VALUE(total_amount, 2)returns thetotal_amountat position 2 of that frame.- The outer
ORDER BYcontrols display order.
Why this and not the default frame
The default frame ends at the current row. On the first row of a partition, position 2 of the frame does not exist yet, so NTH_VALUE(..., 2) returns NULL there. On the second row, position 2 of the frame is the current row itself, so the function returns the current row's value. Neither matches "the customer's second order on every row." Widening the frame to UNBOUNDED FOLLOWING is what makes the answer correct on every row.
The trap
For a customer who has placed only one order, the partition has no row at position 2. NTH_VALUE returns NULL on every row for that customer, which is the correct answer to "what is the second order amount that does not exist." The prompt's contract treats this NULL as the expected value. If a downstream consumer needs a numeric fallback instead, NTH_VALUE does not accept a default argument the way LAG and LEAD do, so the substitution has to be done outside the function with COALESCE.
You practiced NTH_VALUE(column, 2) over the full partition — position 2 means the second record in the ordered partition; the explicit frame ensures the lookup considers every record in the partition, not just records up to the current one.
