Subquery for top category by revenue
SQL
Hard
2 views
Problem Description
Find the product category with the highest revenue. Revenue = SUM(qty * price) from OrderItems joined with Products.
Sample Test Case
Input:
Orders, OrderItems, Products
Constraints
Use derived table and sort by revenue
Official Solution
SELECT category, revenue FROM (SELECT p.category AS category, SUM(oi.qty * oi.price) AS revenue FROM OrderItems oi JOIN Products p ON p.product_id = oi.product_id GROUP BY p.category) t ORDER BY revenue DESC FETCH FIRST 1 ROW ONLY;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!