AdventureWorks Database: Find the statistical variance of the sales quota for each quarter
108. From the following table, write a query in SQL to return the quota date (Year), quarter, and sales quota for a specific salesperson. Additionally, calculate the population variance of the sales quota over the quarters for the year 2012 for this salesperson. Filter the results for the salesperson with businessentityid 277 and the year 2012. Order the results by quarter.
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.