Window Functions

Window functions compute across a set of rows without collapsing them — every input row stays in the output, unlike GROUP BY.

Syntax

function() OVER (
  [PARTITION BY columns]
  [ORDER BY columns]
  [frame_clause]
)

Ranking

SELECT
  name_en, department, cgpa,
  row_number() OVER (PARTITION BY department ORDER BY cgpa DESC) AS row_num,
  rank()       OVER (PARTITION BY department ORDER BY cgpa DESC) AS rnk,
  dense_rank() OVER (PARTITION BY department ORDER BY cgpa DESC) AS dense_rnk,
  ntile(4)     OVER (ORDER BY cgpa DESC) AS quartile
FROM students;

Function	On a tie (3.9, 3.9, 3.8)
row_number()	1, 2, 3 — always distinct
rank()	1, 1, 3 — gaps after ties
dense_rank()	1, 1, 2 — no gaps

Aggregates as window functions

SELECT
  name_en, department, cgpa,
  avg(cgpa) OVER (PARTITION BY department)         AS dept_avg,
  cgpa - avg(cgpa) OVER (PARTITION BY department)  AS diff_from_avg,
  count(*)  OVER (PARTITION BY department)         AS dept_size,
  max(cgpa) OVER ()                                AS overall_best
FROM students;

FILTER combines with a window function too:

SELECT department,
  count(*) FILTER (WHERE cgpa >= 3.5) OVER (PARTITION BY department) AS dept_excellent
FROM students;

Running totals and moving averages

SELECT
  sale_date, amount,
  sum(amount) OVER (ORDER BY sale_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  avg(amount) OVER (ORDER BY sale_date
                    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7d
FROM sales;

Frame types: ROWS counts physical rows; RANGE groups peer rows with equal ORDER BY values; GROUPS (Postgres 11+) counts distinct peer groups. Without an explicit frame, ORDER BY defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

LAG and LEAD

SELECT
  sale_date, amount,
  lag(amount)  OVER (ORDER BY sale_date)             AS prev_day,
  lead(amount) OVER (ORDER BY sale_date)              AS next_day,
  amount - lag(amount) OVER (ORDER BY sale_date)      AS change,
  round(100.0 * (amount - lag(amount) OVER (ORDER BY sale_date))
        / nullif(lag(amount) OVER (ORDER BY sale_date), 0), 1) AS pct_change
FROM sales;

first_value(), last_value() and nth_value() pick a specific row from the frame — remember that last_value() needs an explicit frame of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or it will only see up to the current row.

Named windows

SELECT
  name_en,
  rank()       OVER w AS rnk,
  dense_rank() OVER w AS dense_rnk,
  avg(cgpa)    OVER w AS dept_avg
FROM students
WINDOW w AS (PARTITION BY department ORDER BY cgpa DESC);

Top-N per group

Window functions cannot appear in WHERE, so wrap the query in a CTE:

WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY department ORDER BY cgpa DESC) AS rn
  FROM students
)
SELECT department, name_en, cgpa FROM ranked WHERE rn <= 3 ORDER BY department, rn;

Or use DISTINCT ON for exactly one row per group — a Postgres shortcut covered in the sorting chapter.

Deduplication

WITH dupes AS (
  SELECT id, row_number() OVER (PARTITION BY email ORDER BY created_at) AS rn
  FROM students WHERE email IS NOT NULL
)
DELETE FROM students WHERE id IN (SELECT id FROM dupes WHERE rn > 1);