Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

EasyWindow Functions

LAG: compare each employee salary to previous

The query

SQL
SELECT
  first_name,
  last_name,
  hire_date,
  salary,
  LAG(salary) OVER (ORDER BY hire_date) AS prev_salary,
  salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_diff
FROM employees
ORDER BY hire_date;
Tested against PostgreSQL 16

Note

“LAG accesses previous row. LEAD accesses next row. Second arg = offset (default 1), third = default value.

Tables referenced