Let's focus on
OVER (...), which defines the window. The most basic example is
OVER() and means that the window consists of all rows in the query. Take a look:
That's not a very complicated query, but take a look at the last column:
AVG(salary) means we're looking for the average salary. Where exactly? Everywhere we can, because
OVER() means 'for all rows in the query result'. In others words, we're looking for the average salary in the entire company.
Note that we did NOT group rows.
OVER() makes it possible to show the details of single rows and the result of an aggregating function together. That wouldn't be so easy with
GROUP BY — we would have to write a subquery, which is more complicated and less effective.
OVER() makes our work simple and efficient at the same time.