Get to know tables
Computations with OVER()
Aggregate functions with OVER()
OVER() and WHERE
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.
