SQL Window Functions Cheat Sheet

ROW_NUMBER()

Assigns a unique sequential integer to each row. Ties get different numbers (arbitrary tiebreak).

-- Number each employee within their department by salary
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
-- Result: 1, 2, 3, 4 (no gaps, no ties)

RANK()

Like ROW_NUMBER but ties get the same rank. Leaves gaps after ties.

-- Rank employees by salary (gaps after ties)
RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
-- Result: 1, 2, 2, 4 (gap: 3 is skipped)

DENSE_RANK()

Like RANK but no gaps. Ties get the same rank. The next distinct value gets the next integer.

-- Dense rank: no gaps after ties
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
-- Result: 1, 2, 2, 3 (no gap)

NTILE(n)

Divides the partition into n approximately equal groups and assigns a group number (1 to n) to each row.

-- Divide employees into 4 salary quartiles
NTILE(4) OVER (ORDER BY salary)
-- Result: 1, 1, 2, 2, 3, 3, 4, 4 (for 8 rows)

SUM()

-- Running total of revenue by date
SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- Total revenue per department (on every row)
SUM(revenue) OVER (PARTITION BY dept)

-- 7-day rolling sum
SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

AVG()

-- Running average
AVG(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING)

-- 30-day moving average
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)

-- Department average (compare each row to its group)
AVG(salary) OVER (PARTITION BY dept)

COUNT()

-- Running count
COUNT(*) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- Total rows in partition
COUNT(*) OVER (PARTITION BY dept)

-- Count of non-null values in a window
COUNT(email) OVER (PARTITION BY dept)

MIN() / MAX()

-- Running minimum
MIN(price) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- Max salary in each department
MAX(salary) OVER (PARTITION BY dept)

-- Sliding window min (3-row window)
MIN(temperature) OVER (ORDER BY timestamp ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

LAG(column, offset, default)

Returns the value from a previous row. Offset defaults to 1. Default value used when there is no previous row.

-- Previous day's revenue
LAG(revenue, 1, 0) OVER (ORDER BY date)

-- Revenue from 7 days ago
LAG(revenue, 7) OVER (ORDER BY date)

-- Day-over-day change
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change

LEAD(column, offset, default)

Returns the value from a subsequent row. Mirror of LAG.

-- Next day's revenue
LEAD(revenue, 1) OVER (ORDER BY date)

-- Time to next event (session analysis)
LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
  - event_time AS time_to_next

FIRST_VALUE(column)

Returns the first value in the window frame. Respects the frame clause.

-- First order date per customer
FIRST_VALUE(order_date) OVER (
  PARTITION BY customer_id ORDER BY order_date
  ROWS UNBOUNDED PRECEDING
)

-- Earliest hire in each department
FIRST_VALUE(name) OVER (
  PARTITION BY dept ORDER BY hire_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

LAST_VALUE(column)

Returns the last value in the window frame. Important: the default frame ends at CURRENT ROW, so you usually need to specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

-- Most recent order per customer (needs full frame)
LAST_VALUE(order_date) OVER (
  PARTITION BY customer_id ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

-- WRONG: default frame stops at current row
LAST_VALUE(order_date) OVER (
  PARTITION BY customer_id ORDER BY order_date
)  -- this gives the CURRENT row, not the last

NTH_VALUE(column, n)

Returns the nth value in the window frame. Useful for getting the second-highest, third-most-recent, etc.

-- Second highest salary per department
NTH_VALUE(salary, 2) OVER (
  PARTITION BY dept ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

PERCENT_RANK()

Relative rank as a percentage: (rank - 1) / (total_rows - 1). Returns 0.0 for the first row, 1.0 for the last.

PERCENT_RANK() OVER (ORDER BY salary)
-- Salary at 50th percentile gets ~0.5

CUME_DIST()

Cumulative distribution: fraction of rows with values less than or equal to the current row. Always returns a value between 0 (exclusive) and 1 (inclusive).

CUME_DIST() OVER (ORDER BY score)
-- 0.25 means 25% of rows have a score <= this row's score

Top-N Per Group

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

Year-over-Year Comparison

SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
  revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_revenue;

Percentage of Group Total

SELECT
  dept,
  name,
  salary,
  ROUND(
    100.0 * salary / SUM(salary) OVER (PARTITION BY dept),
    1
  ) AS pct_of_dept
FROM employees;

Consecutive Streaks

-- Find consecutive days of positive revenue
WITH flagged AS (
  SELECT
    date,
    revenue,
    date - ROW_NUMBER() OVER (ORDER BY date)::INT AS grp
  FROM daily_metrics
  WHERE revenue > 0
)
SELECT MIN(date) AS streak_start, MAX(date) AS streak_end,
  COUNT(*) AS streak_length
FROM flagged
GROUP BY grp
ORDER BY streak_length DESC;