MySQL Forums
Forum List  »  Newbie

Best way to get top 3 sales categories from a database
Posted by: Montana Burr
Date: January 04, 2022 10:45AM

I'm going through a practice problem set right now, and one of the problems is asking me to find the three subcategories in which a given set of customers spent the most money.

This involves quite a few joins, so I won't get into the details of the database unless requested.


I actually solved the problem using this query:


WITH
subcategoryExpenditures AS (
SELECT CustomerID,
ProductSubCategoryName,
SUM(UnitPrice) As subcategoryTotal
FROM salesorderheader
JOIN salesorderdetail USING(SalesOrderID)
JOIN product USING(ProductID)
JOIN productsubcategory USING(ProductSubcategoryID)
GROUP BY CustomerID,ProductSubcategoryID
),
expenditureRanks AS (
SELECT subcategoryExpenditures.
*,RANK() OVER (
partition by CustomerID
order by subcategoryTotal desc
) AS subcategoryRank
FROM subcategoryExpenditures
),
topCategory AS (
SELECT CustomerID,ProductSubcategoryName
FROM expenditureRanks
WHERE subcategoryRank = 1
),
secondCategory AS (
SELECT CustomerID,ProductSubcategoryName
FROM expenditureRanks
WHERE subcategoryRank = 2
),
thirdCategory AS (
SELECT CustomerID,ProductSubcategoryName
FROM expenditureRanks
WHERE subcategoryRank = 3
)
SELECT CustomerID,
CONCAT(FirstName, " ", LastName) AS CustomerName,
topCategory.ProductSubcategoryName AS TopProdSubCat1,
secondCategory.ProductSubcategoryName AS TopProdSubCat2,
thirdCategory.ProductSubcategoryName AS TopProdSubCat3
FROM customer
LEFT OUTER JOIN topCategory
USING(CustomerID)
LEFT OUTER JOIN secondCategory
USING(CustomerID)
LEFT OUTER JOIN thirdCategory
USING(CustomerID)
WHERE CustomerID IN (13763,
13836,
20331,
21113,
26313)
ORDER BY CustomerID
;

I'm now wondering if there's a better way to do this. I've thought about refactoring the code to combine the expenditureRanks and subcategoryExpenditures into one CTE, and also creating a CTE that recursively joins on itself and thereby adds three columns representing the three highest product subcategories.

Options: ReplyQuote


Subject
Written By
Posted
Best way to get top 3 sales categories from a database
January 04, 2022 10:45AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.