Find Top 5 Customers for Beverages based on their total purchase value SQL-CodePudding

Here is the link to the Data Set. https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

I have been trying to solve this but couldn't find a way to get the total purchase value while grouping with the customer table

CodePudding user response：

Something like that?

SELECT c.customerid,
       Sum(p.price)
FROM   customers AS c
       INNER JOIN orders AS o
               ON o.customerid = c.customerid
       INNER JOIN orderdetails AS od
               ON od.orderid = o.orderid
       INNER JOIN products AS p
               ON p.productid = od.productid
GROUP  BY c.customerid
ORDER  BY Sum(p.price) DESC
LIMIT  5

Just following on from your quantity comment...

SELECT c.customerid,
       Sum(p.price),
       Sum(p.price * od.quantity)
FROM   customers AS c
       INNER JOIN orders AS o
               ON o.customerid = c.customerid
       INNER JOIN orderdetails AS od
               ON od.orderid = o.orderid
       INNER JOIN products AS p
               ON p.productid = od.productid
GROUP  BY c.customerid
ORDER  BY Sum(p.price) DESC
LIMIT  5

CodePudding user response：

I would recommend using a Common Table Expression (CTE) as, in my experience, it helps with scalability/maintenance down the road and easily enables you to see what the data is under the hood if you wanted to simply run the CTE itself.

I join the Customer to the Order to get the OrderID I join the Order to OrderDetails to get the ProductID and Order Quantity I join the OrderDetails to Products to get the Price I join the Categories to filter for just Beverages

All this is wrapped as a CTE (similar to a subquery), on top of which I can now aggregate at the Customer level and sequence by Order Value in a descending fashion.

with beverage_orders_cte as(
  SELECT c.CustomerName, o.OrderID
  , od.OrderDetailID, od.ProductID, od.Quantity
  , p.ProductName, p.Price
  , od.Quantity * p.Price as OrderVal
  ,cat.CategoryName FROM Customers c
      inner join Orders o
          on c.CustomerID = o.CustomerID
      inner join OrderDetails od
          on o.OrderID = od.OrderID
      inner join Products p 
              on od.ProductID = p.ProductID
      inner join Categories cat
              on p.CategoryID = cat.CategoryID and cat.CategoryID = 1
)
select CustomerName, SUM(OrderVal) as Revenue 
From beverage_orders_cte
Group by CustomerName
Order by Revenue desc
Limit 5

Hope this helps, good luck.