Evaluation Order and Common Problems
Window functions and GROUP BY
Summary and Review

Instruction

As anticipated, the query failed. So, what can we do to make the query work? Again, we'll use a subquery:

SELECT
  Quartile,
  MIN(Views),
  MAX(Views)
FROM (
    SELECT
      Views,
      Ntile(4) OVER(ORDER BY Views DESC) AS Quartile
    FROM Auction
  ) C
GROUP BY Quartile;

We used the window function in the inner query, which is why we could use it for grouping in the external query.

Exercise

Now, divide all auctions into six equal groups, based on AskingPrice in ascending order. Show the columns GroupNo, MinAskingPrice, AvgAskingPrice and MaxAskingPrice for each group. Sort in ascending order by group.

Stuck? Here's a hint!

You will have to use

NTILE(6) OVER(ORDER BY AskingPrice ASC)
as the window function.