SQL Window Functions

PARTITION BY

Divides the result set into independent groups. The window function resets for each partition. Think of it as GROUP BY without collapsing rows. If you omit PARTITION BY, the entire result set is one partition.

ORDER BY

Determines the sequence of rows within each partition. Required for ranking functions (ROW_NUMBER, RANK, DENSE_RANK) and offset functions (LAG, LEAD). For aggregate windows (SUM, AVG, COUNT), ORDER BY turns the aggregate into a running calculation instead of a partition-wide total.

Frame Clause

Controls exactly which rows within the partition the function considers. Specified as ROWS or RANGE followed by a boundary (UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, UNBOUNDED FOLLOWING). The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is not always what you want.

ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition. No ties. If two rows have the same ORDER BY value, the assignment is nondeterministic (the database picks one arbitrarily). This is the most-used window function in data engineering because it powers deduplication: partition by the natural key, order by updated_at DESC, keep row_number = 1.

SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rn
FROM employees;

RANK()

Like ROW_NUMBER but handles ties. When two rows share the same ORDER BY value, they get the same rank. The next rank skips. If two employees tie at rank 2, the next employee gets rank 4 (not 3). This creates gaps in the sequence. Interviewers test whether you understand the gap behavior and when it matters.

SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rnk
FROM employees;
-- Salaries: 90k, 90k, 80k -> Ranks: 1, 1, 3

DENSE_RANK()

Ranks with ties but no gaps. Two employees at rank 2 are followed by rank 3, not rank 4. Use DENSE_RANK when you need N distinct rank levels regardless of ties. A 'top 3 departments by revenue' query using DENSE_RANK guarantees exactly 3 distinct rank values in the output, though more than 3 rows may qualify.

SELECT
  employee_id,
  department,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS drnk
FROM employees;
-- Salaries: 90k, 90k, 80k -> Dense ranks: 1, 1, 2

LAG()

Accesses a value from a previous row within the partition, based on ORDER BY. LAG(column, 1) returns the value from one row back. LAG(column, 2) returns two rows back. If there is no previous row, the result is NULL (or a default you specify as the third argument). This is the go-to function for calculating row-over-row changes: revenue growth, day-over-day deltas, time between events.

SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue, 1) OVER (
    ORDER BY order_date
  ) AS prev_day_revenue,
  daily_revenue - LAG(daily_revenue, 1) OVER (
    ORDER BY order_date
  ) AS day_over_day_change
FROM daily_sales;

LEAD()

The mirror of LAG. Accesses a value from a subsequent row within the partition. LEAD(column, 1) returns the next row's value. Useful for calculating time-to-next-event, gap analysis between consecutive records, and session timeout detection. In clickstream data, LEAD(timestamp) minus the current timestamp gives you the idle time between clicks.

SELECT
  user_id,
  event_timestamp,
  LEAD(event_timestamp, 1) OVER (
    PARTITION BY user_id
    ORDER BY event_timestamp
  ) AS next_event_time,
  LEAD(event_timestamp, 1) OVER (
    PARTITION BY user_id
    ORDER BY event_timestamp
  ) - event_timestamp AS idle_time
FROM clickstream;

NTILE(n)

Divides the partition into n roughly equal buckets and assigns a bucket number (1 through n) to each row. NTILE(4) creates quartiles. NTILE(100) creates percentiles. When the rows do not divide evenly, earlier buckets get one extra row. This is how you build percentile distributions without needing PERCENT_RANK or CUME_DIST.

SELECT
  employee_id,
  salary,
  NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;
-- 100 employees -> 25 per quartile
-- 101 employees -> bucket 1 gets 26, others get 25

Running Total

The most common aggregate window pattern. SUM with ORDER BY accumulates values row by row. This is how you compute cumulative revenue, running balances, or progressive totals without a self-join or correlated subquery.

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_sales;

-- order_date  | daily_revenue | cumulative_revenue
-- 2024-01-01  |         1200  |              1200
-- 2024-01-02  |          800  |              2000
-- 2024-01-03  |         1500  |              3500

Moving Average

A fixed-width sliding window. Instead of accumulating from the beginning, you average over the last N rows. This smooths out daily noise in metrics like DAU, conversion rate, or latency. The frame clause does the work.

SELECT
  report_date,
  daily_active_users,
  AVG(daily_active_users) OVER (
    ORDER BY report_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_avg
FROM app_metrics;

-- First 6 rows have fewer than 7 data points.
-- Day 1: avg of 1 value. Day 2: avg of 2 values. Etc.

Partition-Wide Aggregate

When you omit ORDER BY, the aggregate computes across the entire partition and repeats the same value on every row. This lets you compare each row to its group total without a self-join or subquery.

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

-- Each row shows the individual salary AND what
-- percentage of the department total it represents.

ROWS vs RANGE

ROWS counts physical rows. RANGE groups rows by value. The difference only matters when duplicate ORDER BY values exist.

-- Data: dates with duplicates
-- 2024-01-01: $100
-- 2024-01-01: $200  (same date)
-- 2024-01-02: $150

-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- Row 1: 100
-- Row 2: 100 + 200 = 300
-- Row 3: 100 + 200 + 150 = 450

-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- Row 1: 100 + 200 = 300  (includes all rows with same date)
-- Row 2: 100 + 200 = 300  (same result, same date group)
-- Row 3: 100 + 200 + 150 = 450

The Five Boundaries

Every frame has a start and an end. The start must come before (or equal) the end in the ordering. Here are the five boundary options, from earliest to latest.

UNBOUNDED PRECEDING   -- first row of the partition
N PRECEDING           -- N rows before the current row
CURRENT ROW           -- the current row itself
N FOLLOWING           -- N rows after the current row
UNBOUNDED FOLLOWING   -- last row of the partition

-- Examples:
-- Running total from start to here:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 3-row centered moving average:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- Everything from here to the end:
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- Full partition (same as no frame at all):
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The Default Frame Trap

When you write ORDER BY without an explicit frame, the SQL standard says the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This creates a running calculation, not a partition-wide total. When you omit ORDER BY entirely, there is no frame and the function sees the whole partition. Mixing these up is one of the most common window function bugs.

-- Partition-wide total (no ORDER BY, no frame)
SUM(salary) OVER (PARTITION BY dept)
-- Every row shows the same total: 500000

-- Running total (ORDER BY triggers default frame)
SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date)
-- Row 1: 60000, Row 2: 125000, Row 3: 195000 ...

-- Explicit partition-wide total WITH ordering
SUM(salary) OVER (
  PARTITION BY dept
  ORDER BY hire_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- Every row shows the same total: 500000
-- but rows are still ordered by hire_date

Forgetting ORDER BY inside RANK or DENSE_RANK

RANK() OVER (PARTITION BY department) without ORDER BY is valid syntax in most engines, but every row gets rank 1 because the database has no criteria to differentiate them. This is never what you want. Always include ORDER BY when using ranking functions. ROW_NUMBER without ORDER BY produces nondeterministic results: the row numbering changes between executions.

Confusing ROWS and RANGE in frame clauses

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW counts exactly 2 physical rows back. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW includes all rows whose ORDER BY value is within 2 of the current row's value. With dates, RANGE can pull in multiple rows if several share the same date. Most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you specify ORDER BY without an explicit frame. This default catches people because it is not the same as ROWS UNBOUNDED PRECEDING.

Treating PARTITION BY like GROUP BY

GROUP BY collapses rows: 1000 rows become 10 groups with 10 output rows. PARTITION BY does not collapse anything: 1000 rows in, 1000 rows out. The window function computes a value for each row based on its partition, but every original row survives. Candidates who mix these up try to SELECT non-aggregated columns alongside GROUP BY, get errors, and waste interview time debugging.

Using a window function in WHERE

You cannot write WHERE ROW_NUMBER() OVER (...) = 1. Window functions execute after WHERE. To filter on a window function result, wrap the query in a CTE or subquery and filter in the outer query. This is such a common slip that some interviewers set it as a deliberate trap: they give you a requirement that requires filtering on a rank, and watch whether you reach for a CTE.