SQL Window Functions

Compute a value per row using rows around it — without collapsing rows like GROUP BY does.

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

ROW_NUMBER — Unique Rank (No Ties)

SELECT name, salary, department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;
-- Ties get different numbers: 1, 2, 3, 4 ...

RANK — Ties Get Same Rank, Gap After

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;
-- Ties: 1, 1, 3, 4 (gaps after ties)

DENSE_RANK — Ties Get Same Rank, No Gap

SELECT name, salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;
-- Ties: 1, 1, 2, 3 (no gaps)

Comparison:

NTILE — Divide into N Buckets

-- Assign employees to quartiles by salary
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- Quartile 1 = top 25%, Quartile 4 = bottom 25%

---

Classic Interview Pattern: Top N Per Group

-- Top 3 earners per department
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;

---

Offset Functions

LAG — Access Previous Row

-- Day-over-day revenue change
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;

-- LAG with default (when no previous row exists)
SELECT date, revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue
FROM daily_revenue;

LEAD — Access Next Row

-- Session duration: time until next event
SELECT user_id, event_time,
  LEAD(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  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, cumulative sums — without collapsing rows.

Running Total

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

Partition Running Total (per user)

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

Moving Average (last 7 days)

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

Percent of Total

SELECT name, salary,
  ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_total,
  ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) AS pct_of_dept
FROM employees;

---

Window Frames

ROWS BETWEEN start AND end

start / end options:
  UNBOUNDED PRECEDING  — from the first row of the partition
  N PRECEDING          — N rows before current row
  CURRENT ROW          — the current row
  N FOLLOWING          — N rows after current row
  UNBOUNDED FOLLOWING  — to the last row of the partition

-- Running total: all rows from start to current
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Last 3 rows including current (3-row moving sum)
SUM(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Entire partition (same as no ORDER BY for aggregates)
SUM(amount) OVER (PARTITION BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

ROWS vs RANGE:

• ROWS — physical row positions (exact count back)
• RANGE — logical value range (based on ORDER BY value, not row count)
• Use ROWS by default unless you specifically need RANGE value/tie behavior

-- ROWS: exactly 6 rows back (physical position)
SUM(amount) OVER (
    ORDER BY visited_on
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- RANGE INTERVAL: 6 calendar days back (logical date range)
-- Handles missing/duplicate dates correctly — gaps don't break window
SUM(amount) OVER (
    ORDER BY visited_on
    RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
)

Gotcha: if dates have gaps and you use ROWS 6 PRECEDING, you grab 6 rows not 6 days — wrong for "last 7 days" rolling sum. Use RANGE INTERVAL for date math, ROWS for fixed-window counts.

---

FIRST_VALUE / LAST_VALUE / NTH_VALUE

-- First purchase amount per user, shown on every row
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  -- need full frame for LAST_VALUE!
  ) AS last_order_amount
FROM orders;

LAST_VALUE gotcha: default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the current row, not the partition's last. Always specify full frame.

---

Common Interview Patterns

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

-- Consecutive active days per user
SELECT user_id, MIN(date) AS streak_start, MAX(date) AS streak_end, COUNT(*) AS streak_len
FROM (
  SELECT user_id, date,
    date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS grp
  FROM active_days
) g
GROUP BY user_id, grp;

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

---

Window vs GROUP BY

---

Related

• [[Aggregates]] — GROUP BY, HAVING, aggregate functions
• [[SQL for Interviews]] — classic window function problems
• [[Subqueries & CTEs]] — wrapping window queries in CTEs