"usp_ProductsMostPopular" Stored Procedure

Description:

This stored procedure returns the top5 most purchased items in the Adventure Works Cycles application. It is the engine behind the "Most popular items" user control.

Definition:
CREATE Procedure usp_ProductsMostPopular

AS

SELECT TOP 5 
    SOD.ProductID, 
    SUM(SOD.OrderQty) as TotalNum, 
    P.[Name]
    
FROM    
    Sales.SalesOrderDetail AS SOD
  INNER JOIN Production.Product AS P ON SOD.ProductID=P.ProductID

WHERE
	SOD.SalesOrderID IN (SELECT TOP 1000 SO.SalesOrderID From Sales.SalesOrderHeader AS SO ORDER BY SO.OrderDate DESC)
  
GROUP BY 
    SOD.ProductID, 
    P.[Name]
    
ORDER BY 
    TotalNum DESC;


Database Tables Used:

Product:  The Product table contains the core information about all of the items for sale on the Adventure Works Cycles web site. Its primary key is the ProductID identity field.

SalesOrderDetail:  The SalesOrderDetail table contains detailed product information for each order in the system. For each product in an order, an entry is made into the SalesOrderDetail table containing the ProductID, Quantity and current UnitCost of the item. There is a many to one relationship between SalesOrderDetail table and the SalesOrderHeader table. Note the primary key in the table is both SalesOrderID and LineNumber (since those two columns are the minimum required to guarantee uniqueness of a record).