We can create a more interesting sales report using Window functions and additional calculations in the query, including the gross margin %, total profit, and ranks for product price, sales quantity, and sales rank. The query uses a Common Table Expression (CTE) function to group the data first (as we can’t group using Windows functions).
This is the kind of report decision-makers will be interested in and as more questions come up, additional fields can be added.
USE [ContosoRetailDW];
-- Note: Qty * UnitPrice - Discount = TotalSalesAmount (small descrepency)
-- CTE Function
WITH sub1 AS
(
SELECT p.ProductName, s.UnitCost, s.UnitPrice, SUM(SalesQuantity) as TotalSalesQuantity,
(s.UnitCost * SUM(SalesQuantity)) as TotalCost, SUM(DiscountAmount) as TotalDiscount,
SUM(SalesAmount) as TotalSalesAmount
FROM [dbo].[FactOnlineSales] as s
LEFT OUTER JOIN [dbo].[DimProduct] as p ON s.[ProductKey] = p.[ProductKey]
LEFT OUTER JOIN [dbo].[DimDate] d on s.DateKey = d.DateKey
LEFT OUTER JOIN [dbo].[DimStore] st ON s.StoreKey = st.StoreKey
WHERE
d.CalendarYear = 2007 AND d.CalendarMonthLabel = 'January'
AND
st.StoreName = 'Contoso Europe Online Store'
GROUP BY p.ProductName, s.UnitCost, s.UnitPrice
)
--Main Query referencing the CTE Function
SELECT ProductName, UnitCost, UnitPrice, (UnitPrice-UnitCost)/UnitCost as Margin,
TotalSalesQuantity, format(TotalCost,'$0,,.0M') as TotalCost, format(TotalSalesAmount, '$0,,.0M') as TotalSales,
format(TotalSalesAmount-TotalCost, '$0,,.0M') as TotalProfit,
TotalDiscount,
RANK() OVER(ORDER BY UnitPrice DESC) as PriceRank,
RANK() OVER(ORDER BY TotalSalesQuantity DESC) as QtyRank,
RANK() OVER(ORDER BY TotalSalesAmount DESC) as SalesRank
FROM sub1
ORDER BY SalesRank
ORDER BY SalesRank