Window functions add analytical power to SQL by allowing calculations across sets of rows related to the current row without collapsing results.
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.
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.
PARTITION BY
.ORDER BY
.ROWS BETWEEN
or RANGE
.Modern analytics requires complex calculations that go beyond simple aggregation. Window functions:
SUM()
, AVG()
, MIN()
, and MAX()
can all be used as window functions. Adding OVER()
transforms them from collapsing aggregates to row-aware analytics.
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.LAG()
/ LEAD()
– access preceding or following rows.FIRST_VALUE()
/ LAST_VALUE()
– pull boundary values.NTH_VALUE(expr, n)
– fetch the nth row’s value.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.
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.
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;
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)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
for predictable behavior across databases.ORDER BY
in ranking functions, leading to non-deterministic results. Always specify an order column.OVER()
collapses rows. Add the clause to retain detail.LAG()
without Default causes NULL
s in first rows. Provide a default: LAG(x,1,0)
.In Galaxy’s desktop SQL editor you can:
avg_sales
and press Tab; the AI copilot expands a templated window function with correct partitioning.ROWS BETWEEN
to see an inline explanation of the frame.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.
Window functions enable sophisticated analytics—running totals, rankings, YoY comparisons—without losing row-level detail or relying on complicated subqueries. They are essential for real-time dashboards, data warehouses, and feature engineering in ML pipelines. Because they push computation close to the data, they reduce latency and cost while increasing maintainability.
Most modern relational engines—PostgreSQL, MySQL 8+, SQL Server, Snowflake, BigQuery, Redshift, DuckDB—fully support ANSI SQL window functions.
GROUP BY collapses rows into one output per group, while window functions preserve every row and append analytical calculations as extra columns.
Yes. Galaxy’s context-aware AI copilot suggests partitioning keys, auto-explains frames, and can refactor legacy queries into window-based alternatives.
They can be resource-heavy if misused. Proper indexing, selective columns, and explicit frames typically yield performant queries.