AdventureWorks Database: Compute moving average of yearly sales for each territory
SQL Query - AdventureWorks: Exercise-88 with Solution
88. From the following table write a query in SQL to return a moving average of yearly sales for each territory. Return BusinessEntityID, TerritoryID, SalesYear, SalesYTD, average SalesYTD as MovingAvg, and total SalesYTD as CumulativeTotal.
Sample table: Sales.SalesPerson
businessentityid|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 various columns along with calculated fields from the SalesPerson table
SELECT BusinessEntityID, TerritoryID
,DATE_PART('year', ModifiedDate) AS SalesYear -- Extracting the year from the ModifiedDate column
,CAST(SalesYTD AS VARCHAR(20)) AS SalesYTD -- Converting SalesYTD to VARCHAR data type
,AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATE_PART('year', ModifiedDate)) AS MovingAvg -- Calculating moving average of SalesYTD partitioned by TerritoryID
,SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATE_PART('year', ModifiedDate)) AS CumulativeTotal -- Calculating cumulative total of SalesYTD partitioned by TerritoryID
FROM Sales.SalesPerson -- From the SalesPerson table
-- Filtering the results to include only rows where TerritoryID is NULL or less than 5
WHERE TerritoryID IS NULL OR TerritoryID < 5
-- Ordering the results by TerritoryID and SalesYear
ORDER BY TerritoryID, SalesYear;
Explanation:
- This SQL query retrieves data from the SalesPerson table in the Sales schema.
- It selects various columns from the table along with calculated fields.
- DATE_PART('year', ModifiedDate) extracts the year from the ModifiedDate column.
- CAST(SalesYTD AS VARCHAR(20)) converts the SalesYTD column to VARCHAR data type.
- AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATE_PART('year', ModifiedDate)) calculates the moving average of SalesYTD partitioned by TerritoryID.
- SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATE_PART('year', ModifiedDate)) calculates the cumulative total of SalesYTD partitioned by TerritoryID.
- The WHERE clause filters the results to include only rows where TerritoryID is NULL or less than 5.
- The results are ordered by TerritoryID and SalesYear.
Sample Output:
businessentityid|territoryid|salesyear|salesytd |movingavg |cumulativetotal|
----------------+-----------+---------+------------+--------------------+---------------+
280| 1| 2011.0|1352577.1325|1462795.035400000000| 2925590.0708|
283| 1| 2011.0|1573012.9383|1462795.035400000000| 2925590.0708|
284| 1| 2012.0|1576562.1966|1500717.422466666667| 4502152.2674|
275| 2| 2011.0|3763178.1787|3763178.178700000000| 3763178.1787|
277| 3| 2011.0|3189418.3662|3189418.366200000000| 3189418.3662|
281| 4| 2011.0|2458535.6169|3354952.083300000000| 6709904.1666|
276| 4| 2011.0|4251368.5497|3354952.083300000000| 6709904.1666|
274| | 2010.0|559697.5639 | 559697.563900000000| 559697.5639|
287| | 2012.0|519905.932 | 539801.747950000000| 1079603.4959|
285| | 2013.0|172524.4512 | 417375.982366666667| 1252127.9471|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find the average list price of unique products.
Next: Compute moving average of yearly sales for all sales territories.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-88.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics