Finding Top N Sold Items for Each Group Using SQL

Problem Statement:

Given a table with sales records, including the UPC code of each item sold, sale date, store ID, and total dollar sales, the objective is to retrieve the top 5 sold items for each store in a single SQL query.

Solution:

Using the Common Table Expression (CTE) syntax, we can create a temporary table named 's' to rank the top items within each store based on total dollar sales. Subsequently, we can select the top 5 items for each store using a row number filter.

;WITH s AS (
  SELECT StoreID, UPCCode, tds, rn = ROW_NUMBER()
  OVER (PARTITION BY StoreID ORDER BY tds DESC)
  FROM 
  (
    SELECT StoreID, UPCCode, tds = SUM(TotalDollarSales)
    FROM Sales
    GROUP BY StoreID, UPCCode
  ) AS s2
)
SELECT StoreID, UPCCode, TotalDollarSales = tds
FROM s
WHERE rn 
Explanation:

• The subquery s2 calculates the sum of TotalDollarSales for each combination of StoreID and UPCCode.
• The CTE s creates a ranking column, rn, to determine the row number for each item within each store based on total dollar sales.
• The outer query selects the top 5 items from the CTE for each store, ordered by TotalDollarSales in descending order.