Well done! You're really making progress with OVER().
When using OVER(), you can add a WHERE clause, just as you do in any other query:
SELECT FirstName, LastName, Salary, AVG(Salary) OVER() AS AvgSalary, Salary - AVG(Salary) OVER() AS Difference FROM Employee WHERE DepartmentId = 1;
In this query, we only calculate the salaries in the department with Id = 1. Two exercises ago, we said that OVER() with empty brackets means "for all rows in the query result". The "in the query result" part is very important – window functions work only on the rows returned by the query.
In this case, that means we'll get the salary of each IT department employee and the average salary in that department, not the average for the entire company. That's a very important rule to remember. Window functions are always executed after the WHERE clause, so they work on whatever they find as the result.
Show the FirstName, LastName and Salary of every person who works in departments with ID of 1, 2 or 3, along with the average salary (AvgSalary) calculated for those three departments.
In order to choose DepartmentId 1, 2 or 3, you can use IN(...) in the WHERE clause or a few conditions joined with OR.
