When window functions are evaluated
Window functions and GROUP BY
Summary

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.

Console

Code editor

Result

TableConsole