Evaluation Order and Common Problems
Window functions and GROUP BY
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.
