Brightlane's demand forecasting team is studying longer-horizon spending trajectories. Every order should appear alongside the order amount the same customer placed two purchases later — not just the very next one.
Write a query to return every order's ID, customer ID, order amount, and the order amount from two purchases later for that customer.
Assumptions:
- The order from two purchases later is the order whose
ordered_atis the second-soonest after the current row'sordered_at, restricted to that customer. - For each customer's last two orders — where no second-future order is on record — the two-ahead amount is missing.
- The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andorder_2_ahead. 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,
LEAD(total_amount, 2) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at
) AS order_2_ahead
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
The second argument to LEAD is an offset: LEAD(total_amount, 2) reaches two rows forward inside the partition's order rather than the default one row. Every order ends up next to the amount the same customer placed two orders later, skipping over the immediately-following order entirely.
Clause by clause
SELECT id, customer_id, total_amount, LEAD(total_amount, 2) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS order_2_aheadreturns each order's identifying columns and the amount of the customer's order two purchases ahead.PARTITION BY customer_idconfines the lookup to one customer at a time;ORDER BY ordered_atsets the chronological direction;LEAD(..., 2)advances exactly two positions inside that sequence.FROM ordersreads every order.ORDER BY customer_id, ordered_atsorts the result for chronological reading.
Why the offset and not chaining LEAD
LEAD(total_amount, 2) is a single window-function call; doing this without the offset would require nesting one LEAD inside another, which is not how window functions compose. The offset argument is the supported way to reach an arbitrary fixed distance ahead. The same applies to LAG, which accepts the offset in the same position.
The trap
A customer with only one or two recorded orders has no row that sits two positions ahead, so LEAD(..., 2) returns NULL on those rows. The number of NULLs at the tail of each customer's history equals the offset. On a customer with three orders, the last two rows return NULL for order_2_ahead; on a customer with two orders, both rows return NULL. The NULL is correct; there is no two-ahead order to read. But a downstream calculation that expects every row to carry a numeric value will silently lose rows or propagate NULLs through arithmetic.
You practiced LEAD(column, 2) — the second argument sets the offset distance; 2 jumps forward two records in the ordered partition rather than the default one.
