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

Query 6.6. Best-selling employee per product and year.
WITH SalesProdYearEmp AS (
SELECT P.ProductName, T.Year,
SUM(S.SalesAmount) AS SalesAmount,
E.FirstName + '' + E.LastName AS EmployeeName
FROM Sales S, Employee E, Time T, Product P
WHERE S.EmployeeKey = E.EmployeeKey AND
S.OrderDateKey = T.TimeKey AND
S.ProductKey = P.ProductKey
GROUP BY P.ProductName, T.Year, E.FirstName, E.LastName )
SELECT ProductName, Year,
FORMAT(SalesAmount, ' $###,##0.00 ' )ASTopSales,
EmployeeName AS TopEmployee
FROM SalesProdYearEmp S1
WHERE S1.SalesAmount = (
SELECT MAX(SalesAmount)
FROM SalesProdYearEmp S2
WHERE S1.ProductName = S2.ProductName AND
S1.Year = S2.Year )
The WITH clause computes the total sales by product, year, and employee.
In the query, we select the tuples of this table such that the total sales equals
the maximum total sales for the product and the year.
Query 6.7. Countries that account for top 50% of the sales amount.
WITH SalesCountry AS (
SELECT CountryName, SUM(SalesAmount) AS SalesAmount
FROM Sales S, Customer C, City Y, State T, Country O
WHERE S.CustomerKey = C.CustomerKey AND
C.CityKey = Y.CityKey AND Y.StateKey = T.StateKey AND
T.CountryKey = O.CountryKey
GROUP BY CountryName ),
CumSalesCountry AS (
SELECT S.*, SUM(SalesAmount) OVER (ORDER BY
SalesAmount DESC ROWS UNBOUNDED PRECEDING)
AS CumSalesAmount
FROM SalesCountry S )
SELECT ' All Customers ' AS CountryName,
FORMAT(SUM(SalesAmount), ' $###,##0.00 ' )ASSalesAmount
FROM SalesCountry
UNION
SELECT CountryName,
FORMAT(SalesAmount, ' $###,##0.00 ' )ASSalesAmount
FROM CumSalesCountry
WHERE CumSalesAmount < =
(SELECT MIN(CumSalesAmount) FROM CumSalesCountry
WHERE CumSalesAmount > =
(SELECT 0.5 * SUM(SalesAmount) FROM SalesCountry) ) )
We start by defining the temporary table SalesCountry , which aggregates the
sales amount by country. In the temporary table CumSalesCountry ,foreach
row in SalesCountry , we define a window containing all the rows sorted in
decreasing value of sales amount and compute the sum of the current row