Brightlane's revenue analytics team is benchmarking fulfilment performance and needs to isolate successfully completed orders from the rest of the pipeline.
Write a query to return the average order value for delivered orders in a single column named avg_delivered_value.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - A delivered order has
status = 'delivered'; the average should be computed over those orders only.
Output:
- A single row with one column,
avg_delivered_value.
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
AVG(total_amount) AS avg_delivered_value
FROM
orders
WHERE
status = 'delivered'The shape
The WHERE filter runs first, narrowing orders to only the rows where status = 'delivered'. AVG then computes the average over that filtered set. The result is the average value among delivered orders specifically — the filter determines which rows contribute.
Clause by clause
FROM ordersis the source set: every order Brightlane has processed.WHERE status = 'delivered'runs beforeSELECT, keeping only the rows whosestatusvalue matches the literal string'delivered'. Everything not delivered — cancelled, pending, refunded, in-transit — drops out of the row set the aggregate will see.AVG(total_amount)then averages thetotal_amountcolumn across only those filtered rows. The result is648.0236..., slightly higher than the all-orders average — a small signal that delivered orders carry more value on average than the full pipeline.NULLtotals on delivered orders are still skipped from both the sum and the count, but no non-delivered row contributes to either side.AS avg_delivered_valuelabels the result. Without it, the column would come back asavg, which doesn't say what was averaged or which orders it covers.
Why this and not filter after
Putting WHERE before the aggregate is mandatory — the filter and the aggregate aren't interchangeable in order. SELECT AVG(total_amount) FROM orders returns the average across every order in the table, regardless of status. Filtering "after the fact" by running the aggregate first and then trying to narrow the result doesn't work: the aggregate has already collapsed every row into a single number, and there's nothing left to filter.
The lesson is that WHERE defines what AVG sees. Change the WHERE and you change the answer. The aggregate behaves identically either way; the row set it's working from has changed.
You practiced filtering rows with WHERE before the aggregate runs. The recurring evaluation order: WHERE removes rows first, then the aggregate operates on what's left — "average of delivered orders" is AVG over only the rows WHERE status = 'delivered' admitted.
