Brightlane's customer success team is prioritizing follow-up on completed orders by value, segmented into five value quintiles.
Write a query to return every delivered order's ID, customer ID, total amount, and the order's quintile across delivered orders. Sort the final result by total_amount descending.
Assumptions:
- A delivered order has
status = 'delivered'. Only delivered orders should appear in the result and contribute to the quintile bucketing. - Delivered orders are sorted by
total_amountdescending and assigned to one of five quintiles based on position. Quintile1covers the highest-value fifth of delivered orders by row count; quintile5covers the lowest-value fifth. - When the row count does not divide evenly by
5, the earlier quintiles each receive one extra record. - The final result is sorted by
total_amountdescending.
Output:
- One row per delivered order, with columns
id,customer_id,total_amount, andspend_quintile. Sorted bytotal_amountdescending.
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,
NTILE(5) OVER (
ORDER BY
total_amount DESC
) AS spend_quintile
FROM
orders
WHERE
status = 'delivered'
ORDER BY
total_amount DESCThe shape
Filter to delivered orders first, then run NTILE(5) over a descending total_amount sort so quintile 1 is the highest-value fifth. Sorting descending inside the window is what makes the quintile numbers ascend in the direction the customer success team wants: tier 1 is "follow up first," tier 5 is "lowest priority."
Clause by clause
SELECT id, customer_id, total_amount, NTILE(5) OVER (ORDER BY total_amount DESC) AS spend_quintilereturns the order's identifying columns and its quintile.ORDER BY total_amount DESCinsideOVERis what flips the bucket numbering: the largest order is row 1 in the sort, lands in bucket 1, and tier 1 ends up meaning "highest value."FROM ordersis the row source.WHERE status = 'delivered'keeps only delivered orders. The filter runs before the window function, soNTILEonly ever sees delivered orders and the quintile split is computed across that population alone.ORDER BY total_amount DESCis the outer sort. It prints the result with the highest-value delivered orders at the top, which is also where quintile 1 sits.
Why the filter goes in WHERE and not on the window
The customer success team only cares about delivered orders. They should not contribute to the quintile bucketing either: a quintile cut that included pending and cancelled orders would shift the boundaries away from "the top fifth of delivered orders." WHERE filters before the window function runs, which is the right execution order for this requirement.
The trap
The descending sort inside OVER is what aligns quintile 1 with the highest-value fifth. Writing ORDER BY total_amount (ascending) instead would flip the meaning: tier 1 would become the lowest-value fifth and the customer success team's follow-up list would target the wrong orders. The outer ORDER BY total_amount DESC only controls the printed sequence; the window's sort is what controls the bucket assignment. Both sorts have to agree in direction for the result to read cleanly.
You practiced NTILE(5) OVER (ORDER BY ... DESC) — descending ordering reverses the bucket-to-value mapping; bucket 1 becomes the top values rather than the bottom.
