Window Functions in SQL: A Complete Tutorial with Examples

SQL Window Functions Tutorial

Window functions allow you to perform running totals, moving averages, rankings, gap-filling, and year-over-year comparisons—all in a single SQL query and without rewriting business logic in application code.

What Are Window Functions?

Window functions (also called analytic functions) operate on a set of rows called a window that is defined by the OVER() clause. Unlike aggregate functions that condense rows into a single output, window functions return one value for every row in the original result set while still giving you access to aggregates, rankings, or calculations over that window.

Key Characteristics

• Partitioning – Decide which rows belong to the same group via PARTITION BY.
• Ordering – Specify row order inside each partition with ORDER BY.
• Framing – Optionally limit the range with ROWS BETWEEN or RANGE.
• Non-destructive – Original rows are preserved; results are added as new columns.

Why Are Window Functions Important?

Modern analytics requires complex calculations that go beyond simple aggregation. Window functions:

• Eliminate multi-step subqueries and self-joins, reducing query complexity.
• Improve performance by letting the database engine process data in a single pass.
• Enable real-time dashboards with running totals or cumulative metrics.
• Maintain row granularity, simplifying downstream joins and BI transformations.
• Integrate seamlessly with tools like Galaxy, whose AI copilot can auto-suggest window frames, partitions, and even explain each function inline.

Core Window Functions Explained

Aggregate Variants

SUM(), AVG(), MIN(), and MAX() can all be used as window functions. Adding OVER() transforms them from collapsing aggregates to row-aware analytics.

Ranking Functions

• ROW_NUMBER() – unique sequential number.
• RANK() – same rank for ties, leaving gaps.
• DENSE_RANK() – same rank for ties, no gaps.
• NTILE(n) – divides the partition into n buckets.

Value Functions

• LAG() / LEAD() – access preceding or following rows.
• FIRST_VALUE() / LAST_VALUE() – pull boundary values.
• NTH_VALUE(expr, n) – fetch the nth row’s value.

Detailed Examples

Running Total of Orders

SELECT order_id,
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_sales
FROM orders
ORDER BY order_date;


This adds a cumulative sales column without losing individual orders.

Department-Level Salary Average

SELECT employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;


Each row carries both the employee’s salary and their department’s average, enabling quick variance checks.

Year-over-Year Growth

WITH revenue AS (
SELECT date_trunc('month', created_at) AS month,
SUM(amount) AS monthly_revenue
FROM payments
GROUP BY 1)
SELECT month,
monthly_revenue,
LAG(monthly_revenue) OVER (ORDER BY month) AS last_year,
100.0 * (monthly_revenue - last_year) / last_year AS yoy_growth
FROM revenue;


Top N per Group (Keep Ties)

WITH ranked AS (
SELECT product_id,
category,
sales,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk
FROM product_sales)
SELECT *
FROM ranked
WHERE rnk <= 3; -- top 3 per category (ties included)


Best Practices

• Index on Partition & Order Columns to speed sorting.
• Explicit Frames – Define frames like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for predictable behavior across databases.
• Minimize Columns – Select only needed fields; window queries can be memory-intensive.
• Use CTEs – Break complex logic into readable stages.
• Leverage Tooling – Galaxy’s AI copilot surfaces frame suggestions and highlights inefficient patterns before execution.

Common Mistakes & How to Fix Them

1. Forgetting ORDER BY in ranking functions, leading to non-deterministic results. Always specify an order column.
2. Confusing Aggregates with Window Functions. A missing OVER() collapses rows. Add the clause to retain detail.
3. Using LAG() without Default causes NULLs in first rows. Provide a default: LAG(x,1,0).

Galaxy-Specific Workflow

In Galaxy’s desktop SQL editor you can:

• Type avg_sales and press Tab; the AI copilot expands a templated window function with correct partitioning.
• Hover over ROWS BETWEEN to see an inline explanation of the frame.
• Share the saved query in a Collection for peer endorsement, ensuring everyone reuses the same logic.

Putting It All Together

Window functions unlock advanced analytics inside the database layer, trimming data wrangling time and boosting performance. Whether you are building KPI dashboards, fraud-detection pipelines, or financial models, mastering them is a must. With a modern editor like Galaxy, discovering, writing, and sharing these queries becomes even faster and safer.