Re: Best way to get top 3 sales categories from a database

[Peter Brawley]

As described in the cited articles, eg ...

WITH
  subcategoryExpenditures AS (
    SELECT 
      CustomerID, 
      ProductSubCategory.Name as ProductSubCategoryName, 
      Round( SUM(UnitPrice),2) As subcategoryTotal
    FROM salesorderheader
    JOIN salesorderdetail USING(SalesOrderID)
    JOIN product USING(ProductID)
    JOIN productsubcategory USING(ProductSubcategoryID)
    GROUP BY CustomerID,ProductSubcategoryID
  ),
 expenditureRanks AS (
    SELECT 
      CustomerID, ProductSubcategoryname, subcategoryTotal,
      RANK() OVER ( partition by CustomerID 
                    order by subcategoryTotal desc 
                  ) AS subcategoryRank
    FROM subcategoryExpenditures
  )
SELECT 
  customerID, 
  GROUP_CONCAT( if(subcategoryrank=1,productsubcategoryname,null) ) AS '1st',
  GROUP_CONCAT( if(subcategoryrank=2,productsubcategoryname,null) ) AS '2nd',
  GROUP_CONCAT( if(subcategoryrank=3,productsubcategoryname,null) ) AS '3rd'
FROM expenditureRanks
WHERE CustomerID IN (13763,13836,20331,21113,26313)
GROUP BY CustomerID
ORDER BY CustomerID ;