Rank each product by its total revenue (qty × unit_price) WITHIN its product line, so the top seller in each line is rank 1. Show line, product name, revenue, and the rank.
Rank each product by its total revenue (qty × unit_price) WITHIN its product line, so the top seller in each line is rank 1. Show line, product name, revenue, and the rank.
Answer
SELECT p.line, p.name, SUM(ol.qty * ol.unit_price) AS revenue, RANK() OVER (PARTITION BY p.line ORDER BY SUM(ol.qty * ol.unit_price) DESC) AS rank_in_line FROM products p JOIN order_lines ol ON ol.product_id = p.product_id GROUP BY p.line, p.name ORDER BY p.line, rank_in_line