← All practice problems

Rank items by line value within each order

working · sql🔒Walkthroughs

NEW

Rank items by line value within each order

For every order_item, compute its line value (qty * unit_price) and rank it within its order so the biggest line is rank 1. Carry order.amount_usd onto each row from the joined orders table.

The trick: the join brings the order total onto every item row. The window (PARTITION BY order_id) then operates row-by-row within an order's items — no aggregation, no fan-out double counting.

For tied line values within an order, break ties on product_id ascending.

Columns: order_id, product_id, line_value, amount_usd, item_rank. Sort by order_id, then item_rank ascending.

Tables & sample data

orders

Loading preview…

order_items

Loading preview…

query.sql

Loading…

Loading editor…