AdventureWorks Database: Compute the variance of sales quota for each quarter in a year

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

96. From the following table write a query in SQL to compute the statistical variance of the sales quota values for each quarter in a calendar year for a sales person. Return year, quarter, salesquota and variance of salesquota.

Sample table: sales.salespersonquotahistory

businessentityid|quotadate              |salesquota|rowguid                             |modifieddate           |
----------------+-----------------------+----------+------------------------------------+-----------------------+
             274|2011-05-31 00:00:00.000|     28000|99109bbf-8693-4587-bc23-6036ec89e1be|2011-04-16 00:00:00.000|
             274|2011-08-31 00:00:00.000|      7000|dfd01444-8900-461c-8d6f-04598dae01d4|2011-07-17 00:00:00.000|
             274|2011-12-01 00:00:00.000|     91000|0a69f453-9689-4ccf-a08c-c644670f5668|2011-10-17 00:00:00.000|
             274|2012-02-29 00:00:00.000|    140000|da8d1458-5fb9-4c3e-9ead-8f5ce1393047|2012-01-15 00:00:00.000|
             274|2012-05-30 00:00:00.000|     70000|760cef84-b980-417b-a667-7358c38857f0|2012-04-15 00:00:00.000|
             274|2012-08-30 00:00:00.000|    154000|fb29e024-f26a-49aa-a7cc-c99ae7ba4853|2012-07-16 00:00:00.000|
             274|2012-11-30 00:00:00.000|    107000|13947d2c-a254-47c9-8817-cbd186ffa526|2012-10-16 00:00:00.000|
             274|2013-02-28 00:00:00.000|     58000|bc1a222f-47a0-48d1-9c56-ac873269dc98|2013-01-14 00:00:00.000|
             274|2013-05-30 00:00:00.000|    263000|8b4e3cbf-f0ef-49c5-9a8c-87679055057e|2013-04-15 00:00:00.000|
             274|2013-08-30 00:00:00.000|    116000|e9de95e3-b119-4441-bd1d-b27fc4516022|2013-07-16 00:00:00.000|
             274|2013-11-30 00:00:00.000|     84000|7cea47b5-8391-4414-a866-ff6ec6628cd3|2013-10-16 00:00:00.000|
             274|2014-03-01 00:00:00.000|    187000|0cfb6474-20aa-46d3-a5c0-29dba2eda025|2014-01-15 00:00:00.000|
-- more --

Click to view Full table

Sample Solution:

-- Selecting columns and calculating variance of sales quota over quarters for a specific salesperson in 2012
SELECT quotadate AS Year, date_part('quarter',quotadate) AS Quarter, SalesQuota AS SalesQuota,  
       variance(SalesQuota) OVER (ORDER BY date_part('year',quotadate), date_part('quarter',quotadate)) AS Variance  
-- From the salesperson quota history table
FROM sales.salespersonquotahistory  
-- Filtering for a specific salesperson and year
WHERE businessentityid = 277 AND date_part('year',quotadate) = 2012  
-- Ordering the results by quarter
ORDER BY date_part('quarter',quotadate);

Explanation:

This SQL query retrieves data from the salespersonquotahistory table.
It selects the quotadate as the year, extracts the quarter from the quotadate, and includes the SalesQuota column.
The variance() function calculates the variance of the SalesQuota over quarters, ordered by the year and quarter.
Results are filtered to include only data for a specific salesperson (businessentityid = 277) and year (date_part('year',quotadate) = 2012).
The output is ordered by the quarter.

Sample Output:

year                   |quarter|salesquota|variance             |
-----------------------+-------+----------+---------------------+
2012-02-29 00:00:00.000|    1.0|    952000|                     |
2012-05-30 00:00:00.000|    2.0|   1600000|209952000000.00000000|
2012-08-30 00:00:00.000|    3.0|   1352000|106901333333.33333333|
2012-11-30 00:00:00.000|    4.0|    839000|124705583333.33333333|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the total products ordered in each specified orders.
Next: Populate the variance of all unique values and all values.