# Real SQL Queries - 50 Challenges

Challenging problems from the book - Real SQL Queries - 50 Challenges
SQL
Published

January 22, 2016

## 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)
WHERE OnlineOrderFlag = 0
GROUP BY
SalesPersonID,
DATEPART (YEAR, 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
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,
FQSales = SUM(Subtotal)
WHERE OnlineOrderFlag = 0
GROUP BY
SalesPersonID,
),
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;