Using the following table diagram as a guide answer the following questions:

Question 1

The sales manager would like a query ranking sales people into fifths.  This way they can give awards to the top 20%.

The report should show Look Similar to the following:

What query would you write?

/* Answer */
SELECT p.LastName,
       p.FirstName,
       s.SalesYTD,
       NTILE(5) OVER(ORDER BY s.SalesYTD) SalesQuintile
FROM Sales.SalesPerson s
INNER JOIN Person.Person p on s.BusinessEntityID = p.BusinessEntityID

Follow-on…

Try sorting your answer by the salesperson’s last name.  Did the quintile’s change?

Of course they didn’t! as they’re based on a window function, and the window is independent of the result set’s ordering. 

Question 2

The sales manager would like you to only show sales people that are in the 4th or 5th quin-tile.  What query would you write to do this?

What query would you write?

/* Answer Using CTE */
With SalesRankingCte
as
(
    SELECT p.LastName,
           p.FirstName,
           s.SalesYTD,
           NTILE(5) OVER(ORDER BY s.SalesYTD) SalesQuintile
    FROM Sales.SalesPerson s
    INNER JOIN Person.Person p on s.BusinessEntityID = p.BusinessEntityID
)
SELECT *
FROM SalesRankingCte
WHERE SalesQuintile >= 4

Here is another answer…

/* Answer using derived tables*/
SELECT *
FROM
(
    SELECT p.LastName,
           p.FirstName,
           s.SalesYTD,
           NTILE(5) OVER(ORDER BY s.SalesYTD) SalesQuintile
    FROM Sales.SalesPerson s
    INNER JOIN Person.Person p on s.BusinessEntityID = p.BusinessEntityID
) d
WHERE SalesQuintile >= 4