AdventureWorks Database: Compute moving average of yearly sales for all sales territories
SQL Query - AdventureWorks: Exercise-89 with Solution
89. From the following table write a query in SQL to return a moving average of sales, by year, for all sales territories. Return BusinessEntityID, TerritoryID, SalesYear, SalesYTD, average SalesYTD as MovingAvg, and total SalesYTD as CumulativeTotal.
Sample table: Sales.SalesPersonbusinessentityid|territoryid|salesquota|bonus|commissionpct|salesytd |saleslastyear|rowguid |modifieddate | ----------------+-----------+----------+-----+-------------+------------+-------------+------------------------------------+-----------------------+ 274| | | 0| 0| 559697.5639| 0|48754992-9ee0-4c0e-8c94-9451604e3e02|2010-12-28 00:00:00.000| 275| 2| 300000| 4100| 0.012|3763178.1787| 1750406.4785|1e0a7274-3064-4f58-88ee-4c6586c87169|2011-05-24 00:00:00.000| 276| 4| 250000| 2000| 0.015|4251368.5497| 1439156.0291|4dd9eee4-8e81-4f8c-af97-683394c1f7c0|2011-05-24 00:00:00.000| 277| 3| 250000| 2500| 0.015|3189418.3662| 1997186.2037|39012928-bfec-4242-874d-423162c3f567|2011-05-24 00:00:00.000| 278| 6| 250000| 500| 0.01|1453719.4653| 1620276.8966|7a0ae1ab-b283-40f9-91d1-167abf06d720|2011-05-24 00:00:00.000| 279| 5| 300000| 6700| 0.01| 2315185.611| 1849640.9418|52a5179d-3239-4157-ae29-17e868296dc0|2011-05-24 00:00:00.000| 280| 1| 250000| 5000| 0.01|1352577.1325| 1927059.178|be941a4a-fb50-4947-bda4-bb8972365b08|2011-05-24 00:00:00.000| 281| 4| 250000| 3550| 0.01|2458535.6169| 2073505.9999|35326ddb-7278-4fef-b3ba-ea137b69094e|2011-05-24 00:00:00.000| 282| 6| 250000| 5000| 0.015|2604540.7172| 2038234.6549|31fd7fc1-dc84-4f05-b9a0-762519eacacc|2011-05-24 00:00:00.000| 283| 1| 250000| 3500| 0.012|1573012.9383| 1371635.3158|6bac15b2-8ffb-45a9-b6d5-040e16c2073f|2011-05-24 00:00:00.000| 284| 1| 300000| 3900| 0.019|1576562.1966| 0|ac94ec04-a2dc-43e3-8654-dd0c546abc17|2012-09-23 00:00:00.000| 285| | | 0| 0| 172524.4512| 0|cfdbef27-b1f7-4a56-a878-0221c73bae67|2013-03-07 00:00:00.000| 286| 9| 250000| 5650| 0.018|1421810.9242| 2278548.9776|9b968777-75dc-45bd-a8df-9cdaa72839e1|2013-05-23 00:00:00.000| 287| | | 0| 0| 519905.932| 0|1dd1f689-df74-4149-8600-59555eef154b|2012-04-09 00:00:00.000| 288| 8| 250000| 75| 0.018|1827066.7118| 1307949.7917|224bb25a-62e3-493e-acaf-4f8f5c72396a|2013-05-23 00:00:00.000| 289| 10| 250000| 5150| 0.02|4116871.2277| 1635823.3967|25f6838d-9db4-4833-9ddc-7a24283af1ba|2012-05-23 00:00:00.000| 290| 7| 250000| 985| 0.016|3121616.3202| 2396539.7601|f509e3d4-76c8-42aa-b353-90b7b8db08de|2012-05-23 00:00:00.000|
Sample Solution:
-- Selecting BusinessEntityID, TerritoryID, SalesYear, SalesYTD, MovingAvg, and CumulativeTotal
SELECT BusinessEntityID, TerritoryID
,DATE_PART('year',ModifiedDate) AS SalesYear -- Extracting the year from the ModifiedDate column as SalesYear
,CAST(SalesYTD AS VARCHAR(20)) AS SalesYTD -- Converting SalesYTD to VARCHAR type
,AVG(SalesYTD) OVER (ORDER BY DATE_PART('year',ModifiedDate)) AS MovingAvg -- Calculating moving average of SalesYTD
,SUM(SalesYTD) OVER (ORDER BY DATE_PART('year',ModifiedDate)) AS CumulativeTotal -- Calculating cumulative total of SalesYTD
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5 -- Filtering rows with TerritoryID being NULL or less than 5
ORDER BY SalesYear; -- Ordering the result set by SalesYear
Explanation:
- This SQL query retrieves data from the SalesPerson table.
- It extracts the year from the ModifiedDate column and assigns it as SalesYear.
- SalesYTD is casted to VARCHAR type.
- The AVG() window function calculates the moving average of SalesYTD over the years.
- The SUM() window function calculates the cumulative total of SalesYTD over the years.
- Rows are filtered to include only those where TerritoryID is NULL or less than 5.
- The result set is ordered by SalesYear.
Sample Output:
businessentityid|territoryid|salesyear|salesytd |movingavg |cumulativetotal| ----------------+-----------+---------+------------+--------------------+---------------+ 274| | 2010.0|559697.5639 | 559697.563900000000| 559697.5639| 275| 2| 2011.0|3763178.1787|2449684.049457142857| 17147788.3462| 276| 4| 2011.0|4251368.5497|2449684.049457142857| 17147788.3462| 277| 3| 2011.0|3189418.3662|2449684.049457142857| 17147788.3462| 280| 1| 2011.0|1352577.1325|2449684.049457142857| 17147788.3462| 283| 1| 2011.0|1573012.9383|2449684.049457142857| 17147788.3462| 281| 4| 2011.0|2458535.6169|2449684.049457142857| 17147788.3462| 284| 1| 2012.0|1576562.1966|2138250.719422222222| 19244256.4748| 287| | 2012.0|519905.932 |2138250.719422222222| 19244256.4748| 285| | 2013.0|172524.4512 |1941678.092600000000| 19416780.9260|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Compute moving average of yearly sales for each territory.
Next: Find number of unique titles that employees can hold.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics