Comment by throw_m239339

Introduction to Window Functions with the same examples (salaries per department)

https://www.postgresql.org/docs/13/tutorial-window.html

While you are at it, check out common table expressions as well

https://www.postgresql.org/docs/13/queries-with.html