Top-5 products by total revenue, with quantity sold and a deterministic tie-break
Table order_items(product_id, quantity, unit_price). Return the five products with the highest total revenue, where revenue is SUM(quantity * unit_price). Also return the total quantity sold per product. Order by revenue, highest first, and break ties deterministically so the query is repeatable.
-- aggregate order_items per product, then order and limit to 5Write the query.
Aggregate per product, then order and cap: SELECT product_id, SUM(quantity) AS qty, SUM(quantity*unit_price) AS revenue FROM order_items GROUP BY product_id ORDER BY revenue DESC, product_id LIMIT 5. The secondary product_id key makes ties deterministic.
- ✗Selecting product_id without a GROUP BY on it
- ✗Ranking by quantity instead of revenue
- ✗Using LIMIT with no tie-break, so runs differ
- →How would you return ties at rank 5 instead of cutting them?
- →What changes if unit_price can be NULL?
Group the line items by product, sum revenue and quantity, then order and limit. A second sort key (product_id) makes the ranking repeatable when two products tie on revenue.
SELECT product_id,
SUM(quantity) AS qty,
SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY product_id
ORDER BY revenue DESC, product_id
LIMIT 5;ORDER BY revenue DESC alone is not deterministic: tied products can come back in any order between runs, so the fifth row is unstable. To keep every product tied at the cutoff instead of an arbitrary five, rank with RANK() OVER (ORDER BY revenue DESC) and keep rnk <= 5.
