Real SQL Queries - 50 Challenges

Year over year comparisons

Difficulty: Intermediate

An executive requests data concerning fiscal quarter sales by salesperson. She’d like to see comparisons from the fiscal quarters of $2012$ to the same fiscal quarters of $2011$.

For example, suppose sales for salesperson X totaled $$1,000$ during Fiscal Year $2012$, Fiscal Quarter 2. If sales for salesperson X totaled $$900$ in Fiscal Year $2011$, Fiscal Quarter 2, this reflects about $11.1$ growth between the two periods for salesperson X.

Notes: For Adventure Works, the fiscal year spans July through June. Tax and freight will not be considered with revenue. Dates are based on OrderDate. Disregard online orders. Your output should include the following columns, corresponding to all sales people:

LastName SalesPersonID Fiscal year Fiscal quarter Fiscal quarter sales Sales during the same fiscal quarter of the previous fiscal year Change in revenue between the two periods Percent change in revenue between the two periods

Solution

1. Assign a Fiscal Year and Fiscal Quarter to each order. To get the Fiscal year for each order, add $-6$ months to the year of the order date and extract the year of the new date. To get the Fiscal quarter for each order, add $-6$ months to the year of the order date and extract the quarter of the new date.
2. Filter out online orders i.e. orders where “OnlineOrderFlag = 0”.
3. Group by SalesPersonID, fiscal year, and fiscal quarter.
4. For each combination of SalesPersonID, fiscal year, and fiscal quarter where fiscal year is $2012$, find the correpsonding combination for the previous year i.e. SalesPersonID, fiscal year-1, and fiscal quarter and calculate the percentage change.

Query with CTEs and JOINs

With Sales_FY(SalesPersonID, FY, FQ, FQSales) AS
(
	SELECT
		SalesPersonID,
		DATEPART (YEAR, DATEADD (MONTH, -6, OrderDate)),
		DATEPART (QUARTER, DATEADD (MONTH, -6, OrderDate)),
		FQSales = SUM (Subtotal)
	FROM Sales.SalesOrderHeader
	WHERE OnlineOrderFlag = 0
	GROUP BY 
		SalesPersonID, 
		DATEPART (YEAR, DATEADD (MONTH, -6, OrderDate)), 
		DATEPART (QUARTER,DATEADD (MONTH, -6, OrderDate))
)
SELECT
	P.LastName,
	S1.*,
	S2.FQSales AS SalesSameFQLastYr,
	S1.FQSales - S2.FQSales AS Change,
	((S1.FQSales - S2.FQSales)/ S2.FQSales) * 100 AS [%Change]
FROM Sales_FY S1
	LEFT JOIN Sales_FY S2
		ON S1.SalesPersonID = S2.SalesPersonID
		AND S1.FQ = S2.FQ
		AND S1.FY-1 = S2.FY
	INNER JOIN Person.Person P
		ON S1.SalesPersonID = P.BusinessEntityID
WHERE S1.FY = 2012
ORDER BY SalesPersonID, FY DESC, FQ DESC;

Query with CTEs and Window Functions

WITH Sales_FY(SalesPersonID, FY, FQ, FQSales) AS
(
	SELECT
		SalesPersonID,
		DATEPART(YEAR, DATEADD (MONTH, -6, OrderDate)) AS FY,
		DATEPART(QUARTER, DATEADD (MONTH, -6, OrderDate)),
		FQSales = SUM(Subtotal)
	FROM Sales.SalesOrderHeader
	WHERE OnlineOrderFlag = 0
	GROUP BY 
		SalesPersonID, 
		DATEPART(YEAR, DATEADD (MONTH, -6, OrderDate)), 
		DATEPART(QUARTER,DATEADD (MONTH, -6, OrderDate))
),
Sales_FY_Change(SalesPersonID, FY, FQ, FQSales, SalesSameFQLastYr, PercChange) AS
(
	SELECT 
		SalesPersonID, FY, FQ, FQSales, 
		Lead(FQSales,1) OVER (PARTITION BY SalesPersonID, FQ ORDER BY FY DESC) AS SalesSameFQLastYr,
		(FQSales/Lead(FQSales,1) OVER (PARTITION BY SalesPersonID, FQ ORDER BY FY DESC) - 1)*100 AS PercChange
	FROM Sales_FY
)
SELECT 
	P.LastName, S.*
FROM Sales_FY_Change S, Person.Person P
WHERE BusinessEntityID = SalesPersonID AND FY = 2012
ORDER BY SalesPersonID, FY DESC, FQ DESC;