SQL Window Function Examples

SQL window functions perform calculations across a set of rows related to the current row — without collapsing them into a single group like GROUP BY does. They are essential for analytics: ranking, running totals, moving averages, and accessing previous or next row values in a single query.

ROW_NUMBER - unique row number per partition

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

ROW_NUMBER assigns a unique sequential number to each row within a partition, restarting at 1 for each new partition. No ties: even rows with the same salary get different numbers. Use this to get the Nth row per group.

RANK - ranking with gaps for ties

SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM leaderboard;

RANK assigns the same rank to tied rows but leaves gaps. If two rows are ranked 1st, the next rank is 3rd (not 2nd). Use RANK when gaps in ranking are acceptable and ties should share the same rank.

DENSE_RANK - ranking without gaps

SELECT name, score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM leaderboard;

DENSE_RANK assigns the same rank to ties but does not skip numbers. If two rows are 1st, the next is 2nd (not 3rd). Use DENSE_RANK when you need continuous ranking without gaps — common in leaderboard and competition scenarios.

Get top N rows per group

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

Wrap a ROW_NUMBER window function in a subquery and filter by the row number to get the top N records per group. This is the standard pattern for "top 3 highest-paid employees per department" type queries.

LAG - access the previous row's value

SELECT date, revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

LAG accesses a value from a previous row without a self-join. The second argument is the offset (default 1 = previous row). Use LAG for day-over-day comparisons, period-over-period calculations, and detecting value changes.

LEAD - access the next row's value

SELECT date, revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue
FROM daily_sales;

LEAD accesses a value from a future row. Like LAG but looks forward instead of backward. Useful for calculating time-to-next-event, previewing the next period's value, or flagging when a value will change.

FIRST_VALUE and LAST_VALUE

SELECT name, salary, department,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_in_dept
FROM employees;

FIRST_VALUE returns the first value in the window frame and LAST_VALUE returns the last. Use them to compare each row against the first or last record in its partition — for example, showing the highest salary in each department alongside each employee.

Running total (cumulative sum)

SELECT date, revenue,
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales;

SUM with a window frame computes a running total. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "all rows from the start through the current row". This is the standard cumulative sum pattern for financial dashboards.

Moving average (7-day window)

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

A moving average smooths out short-term fluctuations. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates a 7-row window (6 before + current). Widely used in time-series analysis and financial charting to show trends.

Percentage of total per partition

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

Divide each row's value by the partition total to get a percentage. The SUM OVER without ORDER BY sums all rows in the partition. This calculates each employee's salary as a percentage of their department's total payroll.

How to Use

Window functions use OVER() to define the window — add PARTITION BY to split into groups.
Add ORDER BY inside OVER() for ranking and offset functions.
Use ROWS BETWEEN ... for explicit frame control in aggregate windows.
Wrap in a subquery to filter by window function results (e.g., WHERE rn = 1).

Frequently Asked Questions

What is the difference between window functions and GROUP BY?

GROUP BY collapses multiple rows into one row per group, removing individual row details. Window functions perform calculations across rows but return a value for every row, preserving all original rows. Use GROUP BY for aggregated summaries; use window functions when you need both the detail rows and an aggregate value.

Can I use window functions in a WHERE clause?

No — window functions are evaluated after WHERE and cannot be used directly in WHERE, GROUP BY, or HAVING clauses. Wrap the query in a subquery or CTE and filter in the outer query: SELECT * FROM (SELECT ..., ROW_NUMBER() OVER(...) rn FROM ...) t WHERE rn = 1.

Do all databases support window functions?

Yes, all major databases support them: PostgreSQL (since 8.4), MySQL (since 8.0), SQL Server (since 2005), Oracle (since 8i), and SQLite (since 3.25). If you're on an older MySQL version (5.x), you'll need to use workarounds with user variables or subqueries.

How to Use

Frequently Asked Questions

What is the difference between window functions and GROUP BY?

Can I use window functions in a WHERE clause?

Do all databases support window functions?

Related Tools