Get to know tables
OVER()
Computations with OVER()
7. Computations with OVER()
Aggregate functions with OVER()
OVER() and WHERE
Revision

Instruction

Typically, OVER() is used to compare the current row with an aggregate. For example, we can compute the difference between employee's salary and the average salary. Actually, why don't we calculate the difference between these two values? Take a look:

SELECT
  first_name,
  last_name,
  salary,
  AVG(salary) OVER(),
  salary - AVG(salary) OVER() as difference
FROM employee;

The last column shows the difference between the employee's salary and the average salary. That's the typical usage of window functions: compare the current row with an aggregate for a group of rows. With window functions you can do such comparisons with one simple query.

Exercise

For each employee in table employee, select first and last name, years_worked, average of years spent in the company by all employees, and the difference between the years_worked and the average as difference .

Console

Code editor

Result

TableConsole