Top 2 products per city
SQL
Hard
3 views
Problem Description
Customers(city), Orders, OrderItems. For each city, find top 2 products by quantity sold.
Sample Test Case
Input:
Customers, Orders, OrderItems
Output:
Top products per city
Constraints
Use join + window
Official Solution
SELECT city, product_id, qty_sold FROM (SELECT c.city AS city, oi.product_id AS product_id, SUM(oi.qty) AS qty_sold, ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY SUM(oi.qty) DESC) AS rn FROM Customers c JOIN Orders o ON o.customer_id = c.customer_id JOIN OrderItems oi ON oi.order_id = o.order_id GROUP BY c.city, oi.product_id) t WHERE rn <= 2;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!