PostgreSQL Window Functions — RANK, ROW_NUMBER, LAG, LEAD
Fast recall
Quick revision
Core answer
GROUP BY. The OVER clause defines the window via PARTITION BY (scope) and ORDER BY (ordering). `ROW_NUMBER`/`RANK`/`DENSE_RANK` handle ranking with different tie semantics. `LAG`/`LEAD` access adjacent rows for period-over-period math without self-joins. Frame clauses (ROWS BETWEEN ...) power running totals and moving averages with a single query.Whether you can solve ranking, deduplication, and trend-analysis problems with a single window query instead of correlated subqueries or self-joins, and whether you understand the PARTITION BY / ORDER BY / frame-clause pieces of the OVER clause.
Confusing RANK with ROW_NUMBER when ties exist, or filtering on a window-function result in WHERE — window functions run after WHERE, so the filter must be applied in an outer query.
Distinguish RANK vs DENSE_RANK vs ROW_NUMBER with tie examples, explain the ROWS vs RANGE frame distinction, and show how a supporting index on (PARTITION BY, ORDER BY) columns avoids a spill-to-disk sort.
Interview-ready guide
Interview answer
Window functions compute values across a set of rows related to the current row without collapsing them with GROUP BY. The OVER clause is what makes it a window: PARTITION BY defines the scope, ORDER BY defines the ordering, and an optional frame clause picks a subset relative to the current row.
Ranking — the tie question
- ROW_NUMBER assigns unique sequential integers regardless of ties
- RANK assigns the same number to ties but skips after them, so 1, 1, 3
- DENSE_RANK assigns the same number to ties and does not skip, so 1, 1, 2
- Use ROW_NUMBER when you need strictly one row per position, RANK or DENSE_RANK when ties should genuinely share a rank
LAG and LEAD
- LAG gives the previous row's value, LEAD gives the next — so
amount - LAG(amount) OVER (ORDER BY month)computes month-over-month delta in one pass, no self-join needed LAG(revenue, 12)gives you the same month in the previous year for year-over-year comparisons
Running totals and moving averages
SUM(x) OVER (ORDER BY date)is a running total because the default frame with ORDER BY is everything from the start to the current row- A moving average uses an explicit frame,
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWfor a 7-day trailing window
The common pitfall
- Window functions run after WHERE and GROUP BY but before ORDER BY, so you cannot filter on a window result in WHERE
- Wrap in a CTE and filter in the outer query — the 'top 1 per group' pattern is
ROW_NUMBER()in a CTE plusWHERE rn = 1outside
Performance tip
- An index on the PARTITION BY and ORDER BY columns lets the planner skip the Sort step; without it, a million-row window function can spill to disk
PostgreSQL Window Functions — RANK, ROW_NUMBER, LAG, LEAD
Direct Answer
Window functions compute values across a set of rows related to the current row without collapsing them with GROUP BY. The OVER clause defines the window via PARTITION BY (scope) and ORDER BY (ordering). ROW_NUMBER/RANK/DENSE_RANK handle ranking with different tie semantics. LAG/LEAD access adjacent rows for period-over-period math without self-joins. Frame clauses (ROWS BETWEEN ...) power running totals and moving averages with a single query.
Code Example
-- Sample data
-- employees(id, dept, name, salary)
-- monthly_sales(month, revenue)
-- 1. RANK vs DENSE_RANK vs ROW_NUMBER (tie behaviour)
SELECT
name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rnk,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees;
-- Two ENG employees tied at 95000:
-- name | dept | salary | rnk | dense_rnk | rn
-- Alice | ENG | 95000 | 1 | 1 | 1
-- Bob | ENG | 95000 | 1 | 1 | 2 <- rn always unique
-- Charlie | ENG | 80000 | 3 | 2 | 3 <- RANK skips, DENSE_RANK does not
-- 2. LAG / LEAD — period-over-period without self-joins
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_delta,
ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
/ NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 2) AS yoy_pct
FROM monthly_sales
ORDER BY month;
-- 3. Top-1 earner per department (ROW_NUMBER pattern)
SELECT name, dept, salary
FROM (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn = 1;
-- 4. Running total + 7-day moving average (frame clauses)
SELECT date, sales,
SUM(sales) OVER (ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(sales) OVER (ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;
-- 5. Deduplication — latest row per user
WITH ranked AS (
SELECT u.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
FROM user_events u
)
SELECT * FROM ranked WHERE rn = 1;
-- 6. Percent-of-total without a second query
SELECT product_id, revenue,
revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total,
AVG(revenue) OVER (PARTITION BY category) AS category_avg
FROM product_revenue;
-- 7. EXPLAIN: ensure an index on (PARTITION BY, ORDER BY) columns
-- so WindowAgg consumes presorted input instead of adding a Sort node
CREATE INDEX idx_sales_dept_salary
ON employees (dept, salary DESC);
Common Mistakes & Pitfalls
Confusing RANK with ROW_NUMBER when ties exist, or filtering on a window-function result in WHERE — window functions run after WHERE, so the filter must be applied in an outer query.
Check your understanding
Answer these without looking back at the article.
- What is the difference between RANK, DENSE_RANK, and ROW_NUMBER when ties exist?
- How do you compute a running total or a 7-day moving average using window functions?
- When would you reach for NTILE, PERCENT_RANK, or CUME_DIST?
- How does a supporting index change the execution plan of a window function query?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.