Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 218

CategoryName, Year ORDER BY MonthNumber
ROWS UNBOUNDED PRECEDING), ' $###,##0.00 ' )
AS YTDSalesAmount
FROM SalesByCategoryMonth
ORDER BY CategoryName, Year, MonthNumber

In the temporary table, we aggregate the sales amount by category and
month. In the main query, for each row in the temporary table, we define
a window containing all the rows with the same category and year, order the
rows in the window by month, and compute the sum of the current row and
all the preceding rows in the window.

Query 6.11. Moving average over the last 3 months of the sales amount by
product category.
WITH SalesByCategoryMonth AS (
SELECT CategoryName, Year, MonthNumber, MonthName,
SUM(SalesAmount) AS SalesAmount
FROM SalesS,ProductP,CategoryC,TimeT
WHERE S.OrderDateKey = T.TimeKey AND
S.ProductKey = P.ProductKey AND
P.CategoryKey = C.CategoryKey
GROUP BY CategoryName, Year, MonthNumber, MonthName )
SELECT CategoryName,
MonthName + '' + CAST(Year AS CHAR(4)) AS Month,
FORMAT(SalesAmount, ' $###,##0.00 ' )ASSalesAmount,
FORMAT(AVG(SalesAmount) OVER (PARTITION BY
CategoryName ORDER BY Year, MonthNumber
ROWS 2 PRECEDING), ' $###,##0.00 ' ) AS MovAvg3Months
FROM SalesByCategoryMonth
ORDER BY CategoryName, Year, MonthNumber
In the temporary table, we aggregate the sales amount by category and
month. In the query, we define, for each row of the temporary table, a window
containing all the tuples with the same category, order the tuples in the
window by year and month, and compute the average of the current row and
the two preceding ones.

Query 6.12. Personal sales amount made by an employee compared with
the total sales amount made by herself and her subordinates during 1997.
WITH SalesByEmp1997 AS (
SELECT E.EmployeeKey,
FirstName + '' + LastName AS EmployeeName,
SUM(S.SalesAmount) AS SalesAmount
FROM Sales S, Employee E, Time T
WHERE S.EmployeeKey = E.EmployeeKey AND
S.OrderDateKey = T.TimeKey AND T.Year = 1997
GROUP BY E.EmployeeKey, FirstName, LastName ),
Supervision(SupervisorKey, SubordinateKey) AS (
SELECT SupervisorKey, EmployeeKey

FROM Employee

WHERE SupervisorKey IS NOT NULL