SQL Window Functions: A Complete Tutorial with Examples

What Are SQL Window Functions?

Window functions—also called analytic functions—let you perform calculations across a set of rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse results into a single record, enabling running totals, rankings, moving averages, and more—all while preserving row-level detail.

Why Window Functions Matter

Modern analytics often requires comparing every row to its peers—think of ranking sales reps by month, finding a user’s lifetime spend, or computing a 7-day moving average. Without window functions, you’d resort to self-joins or subqueries, which are slower, harder to read, and error-prone. Window functions boost both performance and clarity, letting you express complex logic in a single pass.

Core Concepts

1. Window Definition

A window is defined by two clauses:

• PARTITION BY — divides rows into logical groups (optional).
• ORDER BY — defines the sequence within each partition (often required).

You can also tailor frame boundaries (ROWS BETWEEN or RANGE BETWEEN) to restrict which rows are visible to the function.

2. Common Window Functions

• SUM(), AVG(), MIN(), MAX() — running totals, moving averages.
• ROW_NUMBER(), RANK(), DENSE_RANK() — row numbering and ranking.
• LAG(), LEAD() — access prior or following rows.
• FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() — positional lookups.
• PERCENT_RANK(), CUME_DIST() — percentiles and cumulative distribution.

Detailed Examples

Running Total of Orders

SELECT order_id,
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM sales.orders
ORDER BY order_date;

Here, SUM() scans rows in chronological order and returns a cumulative total without collapsing individual records.

Customer Lifetime Spend

SELECT customer_id,
order_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) AS lifetime_spend
FROM sales.orders;

PARTITION BY resets the total for each customer.

Top-N per Category

WITH ranked_products AS (
SELECT product_id,
category,
revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM analytics.product_revenue)
SELECT *
FROM ranked_products
WHERE rnk <= 3;

This pattern—rank then filter—returns the three best-selling products in every category.

Week-over-Week Growth

SELECT week_start,
revenue,
LAG(revenue) OVER (ORDER BY week_start) AS prev_week,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week_start))
/ NULLIF(LAG(revenue) OVER (ORDER BY week_start), 0), 2) AS pct_change
FROM analytics.weekly_revenue;


LAG() makes it trivial to compute differences between adjacent rows.

Frame Clauses Explained

By default, many databases use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. But you can tweak frames:

• Running total: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• 7-day moving sum: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
• Entire partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Best Practices

• Always specify ORDER BY for deterministic results, even if the function technically allows omitting it.
• Keep partitions selective. Large partitions can blow up memory usage; filter early if possible.
• Consider indexes. An index on the PARTITION BY and ORDER BY columns often speeds execution.
• Stage complex logic. Break multi-step analytics into CTEs to maintain readability.
• Avoid unnecessary frames. If you need the whole partition, omit the frame clause to let the engine pick an efficient default.

Common Mistakes and How to Fix Them

Using Aggregate Instead of Window Function

Why it’s wrong: Aggregates group rows, destroying detail.
Fix: Swap SUM(amount) for SUM(amount) OVER (...) to preserve rows.

Missing ORDER BY in the Window

Why it’s wrong: Functions like LAG() or ROW_NUMBER() without ORDER BY return non-deterministic results.
Fix: Explicitly order rows inside the OVER() clause.

Filtering on Windowed Columns Without a Subquery

Why it’s wrong: You can’t reference a windowed column in the same SELECT level’s WHERE clause.
Fix: Wrap the query in a CTE or subquery, then filter the outer query.

Working Code Example

-- Monthly Active Users (MAU) with 3-month rolling average
WITH daily AS (
SELECT user_id,
DATE_TRUNC('day', event_time) AS day
FROM app_events
GROUP BY 1,2),
monthly AS (
SELECT DATE_TRUNC('month', day) AS month,
COUNT(DISTINCT user_id) AS dau
FROM daily
GROUP BY 1)
SELECT month,
dau,
ROUND(AVG(dau) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS mau_3mo_avg
FROM monthly
ORDER BY month;


The CTE stages daily activity, aggregates to months, then computes a 3-month moving average—all in one pipeline.

Galaxy and Window Functions

Because Galaxy is a modern SQL editor, its AI copilot can autocomplete OVER clauses, suggest optimal partitions, and even refactor legacy self-join logic into window functions. With Collections, teams can endorse a canonical window-function query—say, the official revenue run rate—so everyone reuses the same logic instead of re-implementing it.

Further Reading

• PostgreSQL official window function tutorial
• Snowflake “Streams & Task” guide for incremental windows
• Analytic Functions chapter in SQL for Data Analytics