AdventureWorks Database: Find the sales quota difference between the current and the first and last quarter
107. From the following table, write a query in SQL to return the BusinessEntityID, quarter, sales year, and sales quota for the current quarter. Additionally, calculate the differences in sales quota from the first and last quarters of the year for each salesperson. Only include records for years after 2005 and for BusinessEntityIDs between 274 and 275. Order the results by BusinessEntityID, sales year, and 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 various columns including BusinessEntityID, quarter, sales year, sales quota for the current quarter,
-- and differences in sales quota from the first and last quarters
SELECT BusinessEntityID,
DATE_PART('quarter', QuotaDate) AS Quarter,
date_part('year',QuotaDate) AS SalesYear,
SalesQuota AS QuotaThisQuarter,
-- Calculating the difference between the current sales quota and the first sales quota of the year
SalesQuota - FIRST_VALUE(SalesQuota) OVER (
PARTITION BY BusinessEntityID, date_part('year',QuotaDate)
ORDER BY DATE_PART('quarter', QuotaDate)
) AS DifferenceFromFirstQuarter,
-- Calculating the difference between the current sales quota and the last sales quota of the year
SalesQuota - LAST_VALUE(SalesQuota) OVER (
PARTITION BY BusinessEntityID, date_part('year',QuotaDate)
ORDER BY DATE_PART('quarter', QuotaDate)
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS DifferenceFromLastQuarter
-- From the Sales.SalesPersonQuotaHistory table
FROM Sales.SalesPersonQuotaHistory
-- Filtering records for years after 2005 and specific BusinessEntityIDs
WHERE date_part('year',QuotaDate) > 2005 AND BusinessEntityID BETWEEN 274 AND 275
-- Ordering the results by BusinessEntityID, SalesYear, and Quarter
ORDER BY BusinessEntityID, SalesYear, Quarter;
Explanation:
- This SQL query retrieves data related to sales quotas for specific quarters and calculates the differences in sales quotas from the first and last quarters of each year for certain BusinessEntityIDs.
- The SELECT clause specifies the columns to be retrieved, including BusinessEntityID, quarter, sales year, sales quota for the current quarter, and the differences from the first and last quarters.
- The FROM clause indicates the source table, Sales.SalesPersonQuotaHistory, from which the data will be retrieved.
- The WHERE clause filters the data to include only records with sales years after 2005 and specific BusinessEntityIDs.
- The OVER clause with the FIRST_VALUE function calculates the difference between the current sales quota and the first sales quota of the year, while the LAST_VALUE function calculates the difference from the last sales quota of the year.
- The PARTITION BY clause partitions the data by BusinessEntityID and sales year, and the ORDER BY clause arranges the data by BusinessEntityID, SalesYear, and Quarter.
Sample Output:
businessentityid|quarter|salesyear|quotathisquarter|differencefromfirstquarter|differencefromlastquarter|
----------------+-------+---------+----------------+--------------------------+-------------------------+
274| 2.0| 2011.0| 28000| 0| -63000|
274| 3.0| 2011.0| 7000| -21000| -84000|
274| 4.0| 2011.0| 91000| 63000| 0|
274| 1.0| 2012.0| 140000| 0| 33000|
274| 2.0| 2012.0| 70000| -70000| -37000|
274| 3.0| 2012.0| 154000| 14000| 47000|
274| 4.0| 2012.0| 107000| -33000| 0|
274| 1.0| 2013.0| 58000| 0| -26000|
274| 2.0| 2013.0| 263000| 205000| 179000|
274| 3.0| 2013.0| 116000| 58000| 32000|
274| 4.0| 2013.0| 84000| 26000| 0|
274| 1.0| 2014.0| 187000| 0| 0|
275| 2.0| 2011.0| 367000| 0| -135000|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Fetch hiredate of last employee in a department for a salary.
Next: Find the statistical variance of the sales quota for each quarter.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.