AdventureWorks Database: Divide rows into defined groups based on SalesYTD
SQL Query - AdventureWorks: Exercise-118 with Solution
118. From the following table write a query in SQL to divide rows into four groups of employees based on their year-to-date sales. Return first name, last name, group as quartile, year-to-date sales, and postal code.
Sample table: Sales.SalesPerson
Sample table: Person.Person
Sample table: Person.Address
Sample Solution:
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
,CAST(SalesYTD as VARCHAR(20) ) AS SalesYTD
, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
Sample Output:
firstname|lastname |quartile|salesytd |postalcode| ---------+-----------------+--------+------------+----------+ Linda |Mitchell | 1|4251368.5497|98027 | Jae |Pak | 1|4116871.2277|98055 | Michael |Blythe | 1|3763178.1787|98027 | Jillian |Carson | 1|3189418.3662|98027 | Ranjit |Varkey Chudukatil| 2|3121616.3202|98055 | José |Saraiva | 2|2604540.7172|98055 | Shu |Ito | 2|2458535.6169|98055 | Tsvi |Reiter | 2|2315185.611 |98027 | Rachel |Valdez | 3|1827066.7118|98055 | Tete |Mensa-Annan | 3|1576562.1966|98055 | David |Campbell | 3|1573012.9383|98055 | Garrett |Vargas | 4|1453719.4653|98027 | Lynn |Tsoflias | 4|1421810.9242|98055 | Pamela |Ansman-Wolfe | 4|1352577.1325|98027 |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return the top ten employees ranked by their salary.
Next: Rank the products in inventory according to their quantities.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join