Window Functions

Window Function Syntax

Window functions compute a value across a set of rows related to the current row.

-- General syntax
function_name(args) OVER (
  [PARTITION BY col1, col2]
  [ORDER BY col3 ASC|DESC]
  [frame_clause]
)

-- Examples: ROW_NUMBER, RANK, DENSE_RANK
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
-- RANK skips numbers after ties; DENSE_RANK does not

LAG & LEAD

Access values from preceding or following rows without a self-join.

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta
FROM monthly_revenue;

-- Next event timestamp (LEAD)
SELECT
  user_id,
  event_type,
  event_at,
  LEAD(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS next_event_at
FROM user_events;

-- LAG/LEAD with offset and default
LAG(salary, 2, 0) OVER (PARTITION BY dept ORDER BY hire_date)
-- returns salary from 2 rows before, or 0 if no such row

Aggregate Window Functions

-- Running total
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Running total per partition (per customer)
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS customer_running_total
FROM orders;

-- Moving average (last 7 days)
SELECT
  dt,
  value,
  AVG(value) OVER (
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7
FROM daily_metrics;

Frame Clauses

Frame clauses define which rows are included in the window relative to the current row.

-- ROWS vs RANGE
-- ROWS: physical row offset
-- RANGE: logical value range (requires ORDER BY)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- from first to current
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING            -- 5-row window centered
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING    -- current to last
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW  -- 7-day range

-- NTILE: divide rows into N buckets
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- PERCENT_RANK and CUME_DIST
SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST()    OVER (ORDER BY salary) AS cume_dist
FROM employees;

-- FIRST_VALUE / LAST_VALUE
SELECT dept, name, salary,
  FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_paid
FROM employees;

Named Windows

-- Reuse window definitions with WINDOW clause
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK()       OVER w AS rank,
  AVG(salary)  OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);