Window functions

Window functions compute per-row results over a partition without collapsing rows like `GROUP BY`. They power rankings, running totals, and period-over-period comparisons in SQL.

					SELECT
  employee_id,
  department,
  salary,
  rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  sum(salary) OVER (PARTITION BY department) AS dept_payroll,
  salary - lag(salary) OVER (PARTITION BY department ORDER BY hire_date) AS raise_delta
FROM employees;

-- Dedupe: keep latest row per key
SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM events
) s WHERE rn = 1;
				

Key clauses: `PARTITION BY` defines groups; `ORDER BY` inside OVER defines frame order; frame specs (`ROWS BETWEEN`) control running windows. DISTINCT ON is an alternative for "top per group" when indexes align.

On interviews: explain the difference from GROUP BY, give a ranking or running-total example, and mention when a window dedupe beats a correlated subquery.

Common pitfalls: missing ORDER BY in OVER (undefined ranking); huge partitions without indexes on partition/sort keys; using windows where a simple aggregate would be clearer and cheaper.

The trade-off is expressive analytics in one query versus sort cost and harder-to-read SQL for junior maintainers.

Checklist:

• State PARTITION BY and ORDER BY intent.
• Know rank vs dense_rank vs row_number semantics.
• Use frames for running sums and moving averages.
• Compare DISTINCT ON or LATERAL for top-N-per-group patterns.