How to Use Window Functions in SQL

CREATE TABLE employees (
  name TEXT,
  department TEXT,
  salary REAL,
  hire_date TEXT
);

INSERT INTO employees VALUES 
  ('Alice', 'Engineering', 95000, '2020-01-15'),
  ('Bob', 'Engineering', 105000, '2019-03-20'),
  ('Charlie', 'Marketing', 72000, '2021-06-10'),
  ('Diana', 'Marketing', 68000, '2020-09-05'),
  ('Eve', 'Engineering', 92000, '2021-02-28'),
  ('Frank', 'Design', 88000, '2019-11-12');

-- ROW_NUMBER
SELECT name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK with PARTITION BY
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- Running total
SELECT name, salary,
  SUM(salary) OVER (ORDER BY salary) as running_total
FROM employees;