AdventureWorks Database: Compare year-to-date sales between employees
SQL Query - AdventureWorks: Exercise-105 with Solution
105. From the following table write a query in SQL to compare year-to-date sales between employees. Return TerritoryName, BusinessEntityID, SalesYTD, and sales of previous year i.e.PrevRepSales. Sort the result set in ascending order on territory name.
Sample table: Sales.vSalesPerson
businessentityid|title|firstname|middlename|lastname |suffix|jobtitle |phonenumber |phonenumbertype|emailaddress |emailpromotion|addressline1 |addressline2|city |stateprovincename|postalcode|countryregionname|territoryname |territorygroup|salesquota|salesytd |saleslastyear|
----------------+-----+---------+----------+-----------------+------+----------------------------+-------------------+---------------+----------------------------+--------------+--------------------------+------------+-------------+-----------------+----------+-----------------+--------------+--------------+----------+------------+-------------+
274| |Stephen |Y |Jiang | |North American Sales Manager|238-555-0197 |Cell |[email protected]| 0|2427 Notre Dame Ave. | |Redmond |Washington |98052 |United States | | | | 559697.5639| 0|
275| |Michael |G |Blythe | |Sales Representative |257-555-0154 |Cell |[email protected]| 1|8154 Via Mexico | |Detroit |Michigan |48226 |United States |Northeast |North America | 300000|3763178.1787| 1750406.4785|
276| |Linda |C |Mitchell | |Sales Representative |883-555-0116 |Work |[email protected] | 0|2487 Riverside Drive | |Nevada |Utah |84407 |United States |Southwest |North America | 250000|4251368.5497| 1439156.0291|
277| |Jillian | |Carson | |Sales Representative |517-555-0117 |Work |[email protected]| 1|80 Sunview Terrace | |Duluth |Minnesota |55802 |United States |Central |North America | 250000|3189418.3662| 1997186.2037|
278| |Garrett |R |Vargas | |Sales Representative |922-555-0165 |Work |[email protected]| 0|10203 Acorn Avenue | |Calgary |Alberta |T2P 2G8 |Canada |Canada |North America | 250000|1453719.4653| 1620276.8966|
279| |Tsvi |Michael |Reiter | |Sales Representative |664-555-0112 |Work |[email protected] | 1|8291 Crossbow Way | |Memphis |Tennessee |38103 |United States |Southeast |North America | 300000| 2315185.611| 1849640.9418|
280| |Pamela |O |Ansman-Wolfe | |Sales Representative |340-555-0193 |Cell |[email protected] | 1|636 Vine Hill Way | |Portland |Oregon |97205 |United States |Northwest |North America | 250000|1352577.1325| 1927059.178|
281| |Shu |K |Ito | |Sales Representative |330-555-0120 |Cell |[email protected] | 2|5725 Glaze Drive | |San Francisco|California |94109 |United States |Southwest |North America | 250000|2458535.6169| 2073505.9999|
282| |José |Edvaldo |Saraiva | |Sales Representative |185-555-0169 |Work |josé[email protected] | 0|9100 Sheppard Avenue North| |Ottawa |Ontario |K4B 1T7 |Canada |Canada |North America | 250000|2604540.7172| 2038234.6549|
283| |David |R |Campbell | |Sales Representative |740-555-0182 |Work |[email protected] | 0|2284 Azalea Avenue | |Bellevue |Washington |98004 |United States |Northwest |North America | 250000|1573012.9383| 1371635.3158|
284|Mr. |Tete |A |Mensa-Annan | |Sales Representative |615-555-0153 |Work |[email protected] | 1|3997 Via De Luna | |Cambridge |Massachusetts |02139 |United States |Northwest |North America | 300000|1576562.1966| 0|
285|Mr. |Syed |E |Abbas | |Pacific Sales Manager |926-555-0182 |Work |[email protected] | 0|7484 Roundtree Drive | |Bothell |Washington |98011 |United States | | | | 172524.4512| 0|
286| |Lynn |N |Tsoflias | |Sales Representative |1 (11) 500 555-0190|Cell |[email protected] | 2|34 Waterloo Road | |Melbourne |Victoria |3000 |Australia |Australia |Pacific | 250000|1421810.9242| 2278548.9776|
287| |Amy |E |Alberts | |European Sales Manager |775-555-0164 |Work |[email protected] | 1|5009 Orange Street | |Renton |Washington |98055 |United States | | | | 519905.932| 0|
288| |Rachel |B |Valdez | |Sales Representative |1 (11) 500 555-0140|Cell |[email protected] | 0|Pascalstr 951 | |Berlin |Hamburg |14111 |Germany |Germany |Europe | 250000|1827066.7118| 1307949.7917|
289| |Jae |B |Pak | |Sales Representative |1 (11) 500 555-0145|Work |[email protected] | 0|Downshire Way | |Cambridge |England |BA5 3HX |United Kingdom |United Kingdom|Europe | 250000|4116871.2277| 1635823.3967|
290| |Ranjit |R |Varkey Chudukatil| |Sales Representative |1 (11) 500 555-0117|Cell |[email protected] | 0|94, rue Descartes | |Bordeaux |Gironde |33000 |France |France |Europe | 250000|3121616.3202| 2396539.7601|
Sample Solution:
-- Selecting the territory name, business entity ID, current year-to-date sales (SalesYTD), and the previous representative's year-to-date sales within each territory
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LAG(SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
-- From the Sales.vSalesPerson view
FROM Sales.vSalesPerson
-- Filtering the data to include only territories specified in the list
WHERE TerritoryName IN ('Northwest', 'Canada')
-- Ordering the results by territory name
ORDER BY TerritoryName;
Explanation:
- This SQL query retrieves data about sales representatives, including their territory name, business entity ID, current year-to-date sales (SalesYTD), and the previous representative's year-to-date sales within each territory.
- The SELECT clause specifies the columns to be retrieved, including the result of the window function LAG.
- The FROM clause indicates the source view Sales.vSalesPerson from which the data will be retrieved.
- The WHERE clause filters the data to include only territories specified in the list ('Northwest', 'Canada').
- The OVER clause with the LAG function calculates the previous representative's year-to-date sales (PrevRepSales) within each territory partitioned by TerritoryName and ordered by SalesYTD in descending order.
- The ORDER BY clause arranges the results alphabetically by territory name for better readability.
Sample Output:
territoryname|businessentityid|salesytd |prevrepsales| -------------+----------------+------------+------------+ Canada | 282|2604540.7172| 0| Canada | 278|1453719.4653|2604540.7172| Northwest | 284|1576562.1966| 0| Northwest | 283|1573012.9383|1576562.1966| Northwest | 280|1352577.1325|1573012.9383|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: return the difference in sales quotas for a specific employee over previous years.
Next: Fetch hiredate of last employee in a department for a salary.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-105.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics