AdventureWorks Database: Find the statistical variance of the sales quota for each quarter

SQL Query - AdventureWorks: Exercise-108 with Solution

108. From the following table write a query in SQL to return the statistical variance of the sales quota values for a salesperson for each quarter in a calendar year. Return quotadate, quarter, SalesQuota, and statistical variance. Order the result set in ascending order on quarter.

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 specific columns from the salespersonquotahistory table
SELECT 
    -- Alias for the quotadate column as Year
    quotadate AS Year, 
    -- Extracting the quarter part from the quotadate and aliasing it as Quarter
    date_part('quarter', quotadate) AS Quarter, 
    -- Selecting SalesQuota column
    SalesQuota AS SalesQuota,  
    -- Calculating the population variance of SalesQuota over the years and quarters
    var_pop(SalesQuota) OVER (
        -- Ordering the data by year and quarter
        ORDER BY date_part('year', quotadate), date_part('quarter', quotadate)
    ) AS Variance  
-- Filtering data for a specific salesperson identified by businessentityid
FROM 
    sales.salespersonquotahistory  
-- Filtering records for the salesperson with ID 277 and the year 2012
WHERE 
    businessentityid = 277 
    AND date_part('year', quotadate) = 2012  
-- Ordering the result set by quarter
ORDER BY 
    date_part('quarter', quotadate);

Explanation:

• This SQL code operates on a table named sales.salespersonquotahistory.
• It retrieves data related to sales quotas for a specific salesperson (identified by businessentityid) for the year 2012.
• The retrieved data includes the year (quotadate aliased as Year), quarter (extracted from quotadate and aliased as Quarter), sales quota (SalesQuota), and the population variance of sales quotas (Variance) over the years and quarters.
• The population variance is calculated using the var_pop function, which calculates the variance of a population.
• The result set is ordered by quarter.

Sample Output:

year                   |quarter|salesquota|variance             |
-----------------------+-------+----------+---------------------+
2012-02-29 00:00:00.000|    1.0|    952000|                    0|
2012-05-30 00:00:00.000|    2.0|   1600000|104976000000.00000000|
2012-08-30 00:00:00.000|    3.0|   1352000| 71267555555.55555556|
2012-11-30 00:00:00.000|    4.0|    839000| 93529187500.00000000|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the sales quota difference between the current and the first and last quarter.
Next: Find different sales quotas for a employee over next years.