Write a query to retrieve the top 3 products for each year. Use OrderQty of SalesOrderDetail to calculate the total sold quantity

Question

The top 3 products have the 3 highest total sold quantities.Also calculate the top 3 products' total sold quantity for the year. Return the data in the following format. Year Total Sale Top5Products 2005 1598 709, 712, 715 2006 5703 863, 715, 712 2007 9750 712, 870, 711 2008 8028 870, 712, 711

USE "AdventureWorks2008R2" for MySQL

USE "AdventureWorks2008R2";

WITH temp as
(
        SELECT YEAR(so.OrderDate) AS Year,so.TotalDue,ProductID,OrderQty,
        DENSE_RANK() OVER(PARTITION BY sod.ProductID ORDER BY OrderQty DESC) AS [Max_Order_Rank]
        FROM Sales.SalesOrderDetail sod
        JOIN Sales.SalesOrderHeader so ON 
            sod.SalesOrderID = so.SalesOrderID
        GROUP BY YEAR(so.OrderDate),ProductID,TotalDue,OrderQty
)
SELECT * FROM temp
ORDER BY Max_Order_Rank DESC;

I'm pretty new to sql and I'm unable to figure out how to how to get the top 3 products from a particular year.

Answer 1

You can use row_number() to get the top 3 products for each year:

select year, productid, total_sold
from (select year, productid, sum(orderqty) as total_sold,
             row_number() over (partition by year order by sum(orderqty) desc) as seqnum
      from temp
      group by year, productid
     ) yp
where seqnum <= 3;