Brightlane's fulfillment team is reviewing delivered order sequences only — pending and cancelled orders are excluded.
Write a query to return every delivered order's ID, customer ID, amount, and the amount of that customer's first delivered order chronologically.
Assumptions:
- A delivered order has
status = 'delivered'. Only delivered orders should appear in the result, and only delivered orders should contribute to the first-order lookup. - A customer's first delivered order is the delivered order with the smallest
ordered_atfor thatcustomer_id. The same first-delivered amount appears on every row sharing acustomer_id. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per delivered order, with columns
id,customer_id,total_amount, andfirst_delivered_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_delivered_amount
FROM
orders
WHERE
status = 'delivered'
ORDER BY
customer_id,
ordered_atThe shape
A WHERE clause runs before a window function evaluates. By the time FIRST_VALUE sees the partition, the non-delivered orders are already gone, so position 1 of each customer's partition is that customer's earliest delivered order. The window function sees the filtered record set, not the original one.
Clause by clause
SELECT id, customer_id, total_amount, FIRST_VALUE(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS first_delivered_amountreturns each delivered order's identifying columns and the customer's first delivered-order amount. The window partitions by customer and orders chronologically;FIRST_VALUEpulls position 1.FROM ordersreads every order.WHERE status = 'delivered'drops every non-delivered row before the window function is evaluated. This is the load-bearing step. The partition thatFIRST_VALUEsees contains only delivered orders.ORDER BY customer_id, ordered_atsorts the printed result chronologically within each customer.
Why this and not filtering after the window function
SQL evaluates WHERE before SELECT, and window functions are part of SELECT. So filtering with WHERE happens first, and the window function operates on the surviving rows. If the requirement were instead "the first order overall, but only show delivered orders," the window function would need to see every order, and the delivered-only restriction would have to be applied later (with a subquery or CTE wrapper). The two phrasings produce different numbers; the prompt asks for the first delivered order, so the WHERE clause goes inside the same query as the window function.
The trap
A customer whose earliest chronological order was cancelled, with later orders delivered, will show their first delivered order in this result, not their first order overall. The number is correct for the question the prompt asked, and wrong for the question a reader might have assumed it asked. The WHERE clause changed what "first" means here. Whenever a window function appears alongside a WHERE filter on the same table, the partition's "first" and "last" refer to positions inside the filtered set.
You practiced FIRST_VALUE over a pre-restricted record set — the WHERE runs first, so the window function's 'first' is the earliest of the surviving records, not the earliest order overall.
