10.8. Aggregate Functions Inside Window Specification

It is possible to use aggregate functions (but not window functions) inside the OVER clause. In that case, first the aggregate function is applied to determine the windows, and only then the window functions are applied on those windows.

ⓘ
Note

When using aggregate functions inside OVER, all columns not used in aggregate functions must be specified in the GROUP BY clause of the SELECT.

Using an Aggregate Function in a Window Specification 

  |select
  |  code_employee_group,
  |  avg(salary) as avg_salary,
  |  rank() over (order by avg(salary)) as salary_rank
  |from employee
  |group by code_employee_group