AdventureWorks Database: Find the differences in sales quota for the next quarters

SQL Query - AdventureWorks: Exercise-111 with Solution

111. From the following table write a query in SQL to obtain the difference in sales quota values for a specified employee over subsequent calendar quarters. Return year, quarter, sales quota, next sales quota, and the difference in sales quota. Sort the result set on year and then by quarter, both in ascending order.

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 
    -- Extracting the year part from the quotadate and aliasing it as Year
    date_part('year', quotadate) AS Year, 

    -- Extracting the quarter part from the quotadate and aliasing it as Quarter
    date_part('quarter', quotadate) AS Quarter, 

    -- Selecting the SalesQuota column
    SalesQuota AS SalesQuota,  

    -- Using the LEAD window function to get the next SalesQuota, defaulting to 0 if not available
    LEAD(SalesQuota, 1, 0) OVER (
        -- Ordering the data by year and quarter
        ORDER BY date_part('year', quotadate), date_part('quarter', quotadate)
    ) AS NextQuota,  

    -- Calculating the difference between SalesQuota and the next SalesQuota
    SalesQuota - LEAD(SalesQuota, 1, 0) OVER (
        -- Ordering the data by year and quarter
        ORDER BY date_part('year', quotadate), date_part('quarter', quotadate)
    ) AS Diff  

-- Filtering data for a specific salesperson identified by businessentityid
FROM 
    sales.salespersonquotahistory  

-- Filtering records for the salesperson with ID 277 and the years 2012 and 2013
WHERE 
    businessentityid = 277 
    AND date_part('year', quotadate) IN (2012, 2013)  

-- Ordering the result set by year and quarter
ORDER BY 
    date_part('year', quotadate), 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 years 2012 and 2013.
• The retrieved data includes the year (quotadate extracted and aliased as Year), quarter (quotadate extracted and aliased as Quarter), sales quota (SalesQuota), the next sales quota (NextQuota) using the LEAD window function, and the difference between the current sales quota and the next sales quota (Diff).
• The LEAD function is used to fetch the next value of SalesQuota, with a default of 0 if the next value is not available.
• The data is ordered by year and quarter.

Sample Output:

year  |quarter|salesquota|nextquota|diff   |
------+-------+----------+---------+-------+
2012.0|    1.0|    952000|  1600000|-648000|
2012.0|    2.0|   1600000|  1352000| 248000|
2012.0|    3.0|   1352000|   839000| 513000|
2012.0|    4.0|    839000|  1369000|-530000|
2013.0|    1.0|   1369000|  1171000| 198000|
2013.0|    2.0|   1171000|   971000| 200000|
2013.0|    3.0|    971000|   714000| 257000|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Compare year-to-date sales between employees for specific terrotery.
Next: Obtain the salary percentile of each employee for a department.