SQL Window Functions Cheat Sheet

-- Number each employee within their department by salary
SELECT emp_name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
LIMIT 20;
-- rn: 1, 2, 3, 4 (no gaps, no ties)

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

-- Rank employees by salary (gaps after ties)
SELECT emp_name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
LIMIT 20;
-- rnk: 1, 2, 2, 4 (gap: 3 is skipped)

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

-- Dense rank: no gaps after ties
SELECT emp_name, department, salary,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees
LIMIT 20;
-- drnk: 1, 2, 2, 3 (no gap)

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

-- Divide employees into 4 salary quartiles
SELECT emp_name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees
LIMIT 20;
-- quartile: 1, 1, 2, 2, 3, 3, 4, 4 (spread across rows)

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

-- Running total of revenue, plus partition total and 7-row rolling sum
SELECT
  transaction_id,
  transaction_date,
  total_amount,
  SUM(total_amount) OVER (ORDER BY transaction_date ROWS UNBOUNDED PRECEDING) AS running_total,
  SUM(total_amount) OVER (PARTITION BY product_id) AS product_total,
  SUM(total_amount) OVER (ORDER BY transaction_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7
FROM transactions
LIMIT 20;

-- Running average, 5-row moving average, and department average
SELECT
  emp_name,
  department,
  hire_date,
  salary,
  AVG(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_avg,
  AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg_5,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
LIMIT 20;

-- Running count, partition row count, and non-null count in a window
SELECT
  emp_name,
  department,
  hire_date,
  COUNT(*) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_count,
  COUNT(*) OVER (PARTITION BY department) AS dept_count,
  COUNT(manager_id) OVER (PARTITION BY department) AS dept_with_manager
FROM employees
LIMIT 20;

-- Running minimum, group maximum, and 3-row sliding window min
SELECT
  emp_name,
  department,
  hire_date,
  salary,
  MIN(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_min,
  MAX(salary) OVER (PARTITION BY department) AS dept_max,
  MIN(salary) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sliding_min
FROM employees
LIMIT 20;

-- Previous transaction's amount and the change from it
SELECT
  transaction_id,
  transaction_date,
  total_amount,
  LAG(total_amount, 1, 0) OVER (ORDER BY transaction_date) AS prev_amount,
  total_amount - LAG(total_amount, 1, 0) OVER (ORDER BY transaction_date) AS change_from_prev
FROM transactions
LIMIT 20;

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

-- Next transaction's amount per user (session-style analysis)
SELECT
  user_id,
  transaction_date,
  total_amount,
  LEAD(total_amount, 1) OVER (PARTITION BY user_id ORDER BY transaction_date) AS next_amount
FROM transactions
LIMIT 20;

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

-- Earliest-hired employee name in each department
SELECT
  emp_name,
  department,
  hire_date,
  FIRST_VALUE(emp_name) OVER (
    PARTITION BY department ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_hired
FROM employees
LIMIT 20;

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

-- Latest-hired employee name per department (needs the full frame)
SELECT
  emp_name,
  department,
  hire_date,
  LAST_VALUE(emp_name) OVER (
    PARTITION BY department ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_hired
FROM employees
LIMIT 20;

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.

-- Second highest salary per department
SELECT
  emp_name,
  department,
  salary,
  NTH_VALUE(salary, 2) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest
FROM employees
LIMIT 20;

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

SELECT emp_name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees
LIMIT 20;
-- Salary at the 50th percentile gets ~0.5

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

SELECT emp_name, salary,
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees
LIMIT 20;
-- 0.25 means 25% of rows have a salary <= this row's salary

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).

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

WITH monthly_revenue AS (
  SELECT
    substr(transaction_date, 1, 7) AS month,
    SUM(total_amount) AS revenue
  FROM transactions
  GROUP BY substr(transaction_date, 1, 7)
)
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;

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

-- Find consecutive days of positive revenue
WITH daily_metrics AS (
  SELECT
    substr(transaction_date, 1, 10) AS day,
    SUM(total_amount) AS revenue
  FROM transactions
  GROUP BY substr(transaction_date, 1, 10)
),
flagged AS (
  SELECT
    day,
    revenue,
    julianday(day) - ROW_NUMBER() OVER (ORDER BY day) AS grp
  FROM daily_metrics
  WHERE revenue > 0
)
SELECT MIN(day) AS streak_start, MAX(day) AS streak_end,
  COUNT(*) AS streak_length
FROM flagged
GROUP BY grp
ORDER BY streak_length DESC;