When window functions are evaluated
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 6 equal groups based on the asking_price in ascending order. Show columns group_no, minimal, average and maximal value for that group. Sort by the group in ascending order.
Stuck? Here's a hint!
You will have to use
ntile(6) OVER(ORDER BY asking_price)as the window function.
