w3resource

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|

Click to view Full table

Sample Solution:

SELECT BusinessEntityID, TerritoryID   
   ,DATE_PART('year',ModifiedDate) AS SalesYear  
   ,cast(SalesYTD as VARCHAR(20)) AS  SalesYTD  
   ,AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATE_PART('year',ModifiedDate)) AS MovingAvg  
   ,SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATE_PART('year',ModifiedDate)) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5  
ORDER BY TerritoryID,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.



Follow us on Facebook and Twitter for latest update.