SQL Window Functions

Compute a value per row using rows around it — without collapsing rows the way GROUP BY does. This is the single most-tested "senior-level" SQL topic.

function_name() OVER (
  PARTITION BY col     -- reset window per group (optional)
  ORDER BY col         -- row order within window (required for ranking/lag/running totals)
  ROWS/RANGE BETWEEN ... AND ...  -- frame definition (optional)
)

Ranking Functions — the Three-Way Comparison

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,     -- ties get DIFFERENT numbers: 1,2,3,4
  RANK()       OVER (ORDER BY salary DESC) AS rnk,    -- ties get SAME rank, gap after: 1,1,3,4
  DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk    -- ties get SAME rank, no gap: 1,1,2,3
FROM employees;

NTILE(4) divides ordered rows into 4 roughly-equal buckets (quartiles) — a different question from ranking (which position) versus bucketing (which quartile).

Classic Pattern: Top-N Per Group

SELECT department, name, salary FROM (
  SELECT department, name, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

This exact shape (rank inside a subquery, filter on the rank in the outer query) is the standard answer to "top N per group" in nearly every dialect — worth having memorized cold.

Offset Functions — LAG / LEAD

SELECT date, revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_delta
FROM daily_revenue;

-- LEAD: time until the next event, per user
SELECT user_id, event_time,
  LEAD(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) - event_time AS gap
FROM events;

Aggregate Window Functions — Running Totals, Moving Averages

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total
FROM transactions;

SELECT user_id, date, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS user_running_total
FROM transactions;

SELECT date, amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

Window Frames — ROWS vs RANGE

UNBOUNDED PRECEDING | N PRECEDING | CURRENT ROW | N FOLLOWING | UNBOUNDED FOLLOWING

ROWS counts physical row positions; RANGE uses the logical value of ORDER BY (e.g., actual calendar days). This distinction matters concretely: with gaps or duplicate dates, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW grabs exactly 6 rows back, not 6 days — if a day is missing, the window silently spans more than a week of calendar time. For a genuine "last 7 calendar days" rolling window, use RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW, which handles gaps correctly by value, not row count.

FIRST_VALUE / LAST_VALUE — a Real Gotcha

SELECT user_id, order_date, amount,
  FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY user_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   -- REQUIRED — see below
  ) AS last_order_amount
FROM orders;

The gotcha: the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — under that default, LAST_VALUE returns the current row's value, not the partition's actual last row, because the frame's own upper bound is "current row." You must explicitly widen the frame to the full partition for LAST_VALUE to mean what it sounds like it means.

Common Interview Patterns

-- Second highest salary
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) r WHERE rnk = 2;

-- Running total that resets each month
SELECT date, amount,
  SUM(amount) OVER (PARTITION BY DATE_TRUNC('month', date) ORDER BY date) AS monthly_running_total
FROM transactions;

Window Function vs GROUP BY

Interview Drills

1. What's the difference between RANK and DENSE_RANK on tied values? — RANK leaves a gap after ties (1, 1, 3, 4); DENSE_RANK doesn't (1, 1, 2, 3) — the gap in RANK reflects "3 rows outrank this position," while DENSE_RANK counts distinct rank values, not row positions.
2. LAST_VALUE in a query returns the current row's value instead of the partition's true last value. Why, and how do you fix it? — The default window frame ends at CURRENT ROW, so from any given row's perspective, "last value in the frame" IS that row itself; fix by explicitly specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to widen the frame to the entire partition.
3. Why would ROWS BETWEEN 6 PRECEDING AND CURRENT ROW give a wrong answer for "trailing 7 calendar days" if some dates are missing from the data? — ROWS counts physical row positions, not calendar time — with a gap in dates, 6 rows back could span more than 6 actual days. Use RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW to window by actual date value instead of row count.

Cross-links

[[SQL Aggregates & GROUP BY]] · [[SQL Subqueries & CTEs]] · [[SD Interview Framework & Pitfalls]]