Q:

SQL views, Groupping + inner join the same table

This is my table:
Using this query, I am getting most sold items:
SELECT [Purchased Item], SUM([Overall Quantity purchased] )
FROM ReportDraft 
GROUP BY [Purchased Item]
ORDER BY SUM([Overall Quantity purchased] )

This returns items and total quantity purchased by customer.
how I can create a table like this  
ItemName | Total quantity purchased | Customer who purchased most | Customer quantity bought

Pie--------|---------6------------|---------------Tonya----------|--------4------------|
Big Burger-|---------3------------|---------------Tonya----------|--------3------------| and etc

Thank you 

A:

Why do you post a new question instead of nodifying/extending the previous one?
WITH cte AS
 (
   SELECT [Purchased Item], 

      -- quantity per item
      SUM(SUM([Overall Quantity purchased]))
      OVER (PARTITION BY [Purchased Item]) AS "Total quantity purchased",

      [Customer name],

      -- quantity per item/customer
      SUM([Overall Quantity purchased]) AS customer_qantity,

      -- rank the customer quantity per item
      ROW_NUMBER()
      OVER (PARTITION BY [Purchased Item]
            ORDER BY SUM([Overall Quantity purchased]) DESC) AS rn
   FROM ReportDraft 
   GROUP BY [Purchased Item], [Customer name]
 )
SELECT *
FROM cte
WHERE rn = 1
ORDER BY "Total quantity purchased" DESC

