Brightlane's category manager wants to see how orders rank within each customer's history by amount, using a position method that keeps tied amounts together and never skips a position.
Write a query to return the ID, customer ID, and total amount of every order, plus the order's dense rank within that customer's orders by total_amount in descending order. Sort the final result by customer_id ascending and total_amount descending.
Assumptions:
- Within each customer's orders, rank
1goes to the highesttotal_amount. Rank values restart for each customer. - Orders for the same customer with the same
total_amountreceive the same rank, and the next rank value is always exactly one higher. - The final result is sorted by
customer_idascending and then bytotal_amountdescending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andcustomer_dense_rank. Sorted bycustomer_idascending, thentotal_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,
DENSE_RANK() OVER (
PARTITION BY
customer_id
ORDER BY
total_amount DESC
) AS customer_dense_rank
FROM
orders
ORDER BY
customer_id,
total_amount DESCThe shape
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) ranks each customer's orders by amount with no gaps after ties, restarting at 1 for every customer. The outer ORDER BY customer_id, total_amount DESC controls how the rows print but has no effect on the rank values themselves.
Clause by clause
SELECT id, customer_id, total_amount, DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_dense_rankreturns each order's identifying columns and a per-customer dense rank.PARTITION BY customer_idmakes each customer their own window;ORDER BY total_amount DESCsorts inside each window from largest to smallest order;DENSE_RANKassigns1to a customer's biggest order (or orders, if tied) and consecutive integers to each next-distinct amount.FROM ordersreads every order.ORDER BY customer_id, total_amount DESCsorts the printed result set so each customer's orders appear together and within a customer they read from biggest to smallest. This is the query's outer sort, not the window's, and the two run independently.
Why DENSE_RANK over RANK here
The choice maps to what the consumer reads off the column. DENSE_RANK returns consecutive integers across distinct amounts, so customer rank 2 means "this customer's second-tier order amount." RANK would leave gaps after ties, which makes the column harder to read as a within-customer tier indicator. On tie-free data the two functions agree; the divergence shows up only on customers whose orders include duplicate amounts, where DENSE_RANK keeps the integers tight.
The trap
The outer ORDER BY and the window's ORDER BY look like they might interact, and they do not. The window function is fully computed before the outer ORDER BY runs; the ranks are determined entirely by what is inside OVER (...). Reordering or removing the outer sort changes only the row order in the output, not a single rank value. The mistake is reading the outer sort as participating in the ranking definition. It does not. If a customer's biggest order needs to appear first in the output, the outer sort is what does that work; if it needs to be ranked 1, the window's ORDER BY is what does that work.
You practiced DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) — gap-free per-partition ranking, the right shape when 'rank within group' should produce continuous integers regardless of ties.
