AdventureWorks Database: Compute moving average of yearly sales for all sales territories

Last update on December 20 2024 11:58:05 (UTC/GMT +8 hours)

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.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:

-- 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.