Window Functions in SQL for Data Engineers
The window function patterns data engineers use every week, each run on the real Chinook database: ROW_NUMBER for the latest row per key, RANK and DENSE_RANK on ties, LAG for gaps between events, frames for running totals and moving averages, and the default RANGE frame that silently changes a running total.
GloryTecks Team
Training & Placement Faculty
Last updated: 1 October 2026
GROUP BY collapses rows into one per group. Window functions compute across a group of rows while keeping every row, which is exactly what pipelines need for deduplication, change detection, rankings and running totals. A window function is any function with an OVER clause, which defines the partitioning and ordering of the rows it sees (Microsoft Learn: OVER clause). Five patterns cover most data engineering uses.
The patterns, run on real data
The queries below run on the Invoice table of the Chinook sample database (Chinook on GitHub) in SQLite, which has supported window functions since version 3.25 (SQLite docs: window functions). The window syntax is standard SQL and works the same way in SQL Server, PostgreSQL, Snowflake, BigQuery and Spark SQL.
import sqlite3
import urllib.request
from pathlib import Path
url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
if not Path("chinook.sqlite").exists():
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")
def show(title, sql):
cur = con.execute(sql)
print(f"-- {title}")
print(" | ".join(d[0] for d in cur.description))
for row in cur:
print(" | ".join(str(v) for v in row))
show("1. latest invoice per customer: ROW_NUMBER", """
WITH ranked AS (
SELECT CustomerId, InvoiceId, date(InvoiceDate) AS invoice_date, Total,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY InvoiceDate DESC, InvoiceId DESC) AS rn
FROM Invoice)
SELECT COUNT(*) AS customers, MIN(invoice_date) AS oldest_latest, MAX(invoice_date) AS newest_latest
FROM ranked WHERE rn = 1""")
show("2. ties: ROW_NUMBER vs RANK vs DENSE_RANK", """
SELECT InvoiceId, Total,
ROW_NUMBER() OVER (ORDER BY Total DESC, InvoiceId) AS row_number,
RANK() OVER (ORDER BY Total DESC) AS rank,
DENSE_RANK() OVER (ORDER BY Total DESC) AS dense_rank
FROM Invoice ORDER BY Total DESC, InvoiceId LIMIT 6""")
show("3. gap since the previous purchase: LAG", """
SELECT InvoiceId, date(InvoiceDate) AS invoice_date,
CAST(julianday(InvoiceDate) - julianday(LAG(InvoiceDate) OVER (PARTITION BY CustomerId ORDER BY InvoiceDate)) AS INTEGER) AS days_since_previous
FROM Invoice WHERE CustomerId = 1 ORDER BY InvoiceDate LIMIT 4""")
show("4. running total and 3-month moving average: frames", """
WITH monthly AS (
SELECT strftime('%Y-%m', InvoiceDate) AS month, ROUND(SUM(Total), 2) AS revenue
FROM Invoice WHERE InvoiceDate >= '2013-01-01' GROUP BY month)
SELECT month, revenue,
ROUND(SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS running_total,
ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM monthly ORDER BY month LIMIT 5""")
show("5. the default frame trap: RANGE vs ROWS when the ordering has ties", """
SELECT * FROM (
SELECT InvoiceId, date(InvoiceDate) AS invoice_date, Total,
ROUND(SUM(Total) OVER (ORDER BY date(InvoiceDate)), 2) AS default_range,
ROUND(SUM(Total) OVER (ORDER BY date(InvoiceDate), InvoiceId ROWS UNBOUNDED PRECEDING), 2) AS explicit_rows
FROM Invoice)
WHERE InvoiceId BETWEEN 6 AND 9 ORDER BY InvoiceId""")
con.close()-- 1. latest invoice per customer: ROW_NUMBER
customers | oldest_latest | newest_latest
59 | 2012-05-30 | 2013-12-22
-- 2. ties: ROW_NUMBER vs RANK vs DENSE_RANK
InvoiceId | Total | row_number | rank | dense_rank
404 | 25.86 | 1 | 1 | 1
299 | 23.86 | 2 | 2 | 2
96 | 21.86 | 3 | 3 | 3
194 | 21.86 | 4 | 3 | 3
89 | 18.86 | 5 | 5 | 4
201 | 18.86 | 6 | 5 | 4
-- 3. gap since the previous purchase: LAG
InvoiceId | invoice_date | days_since_previous
98 | 2010-03-11 | None
121 | 2010-06-13 | 94
143 | 2010-09-15 | 94
195 | 2011-05-06 | 233
-- 4. running total and 3-month moving average: frames
month | revenue | running_total | moving_avg_3
2013-01 | 37.62 | 37.62 | 37.62
2013-02 | 27.72 | 65.34 | 32.67
2013-03 | 37.62 | 102.96 | 34.32
2013-04 | 33.66 | 136.62 | 33.0
2013-05 | 37.62 | 174.24 | 36.3
-- 5. the default frame trap: RANGE vs ROWS when the ordering has ties
InvoiceId | invoice_date | Total | default_range | explicit_rows
6 | 2009-01-19 | 0.99 | 35.64 | 35.64
7 | 2009-02-01 | 1.98 | 39.6 | 37.62
8 | 2009-02-01 | 1.98 | 39.6 | 39.6
9 | 2009-02-02 | 3.96 | 43.56 | 43.561. The latest row per key: ROW_NUMBER
Numbering rows within each customer, newest first, and keeping number 1 returns exactly one row per customer: 59 customers, 59 latest invoices. This is the standard way to deduplicate, to pick the current version of a record from a change log, and to build "latest state" tables from event data. The tie-breaker matters: ordering by InvoiceDate alone would leave the choice arbitrary when two invoices share a timestamp, so a unique column, here InvoiceId, is added to make the result repeatable.
2. Ranking with ties: RANK and DENSE_RANK
Invoices 96 and 194 both total 21.86. ROW_NUMBER gives them 3 and 4, an arbitrary split made repeatable only by the tie-breaker. RANK gives both 3 and then skips to 5. DENSE_RANK gives both 3 and continues with 4. Choose by the question: ROW_NUMBER for "exactly N rows", RANK for competition-style ranking, DENSE_RANK for "the top N distinct values".
3. Comparing with the previous row: LAG
LAG reads a value from the previous row in the partition, and LEAD from the next. For customer 1, the gap between purchases is 94 days twice, then 233. The first row has no previous row, so the result is null, shown as None. The same pattern finds gaps in sequences, detects when a value changed between consecutive records, and computes session boundaries from event times.
4. Running totals and moving averages: frames
A frame limits which rows of the partition an aggregate sees. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a running total; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three-row moving average. Note the first two months: the moving average covers only the rows that exist, so January's "three-month" average is one month. Filter those rows out or label them if that matters.
5. The default frame trap
If you write ORDER BY in the OVER clause and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, in SQL Server and SQLite alike. RANGE treats rows with the same ordering value as peers and includes all of them. Invoices 7 and 8 share a date, so under the default frame invoice 7's running total is already 39.6, including invoice 8, while the explicit ROWS frame gives 37.62 then 39.6. The default is not wrong, but it is rarely what a row-by-row running total intends. Write the frame explicitly, and order by a unique key.
Other functions worth knowing
- NTILE(n) splits a partition into n roughly equal buckets, useful for quartiles and deciles.
- FIRST_VALUE and LAST_VALUE return a value from the first or last row of the frame. LAST_VALUE is a classic trap: with the default frame, the frame ends at the current row, so it returns the current row's own value. Give it a frame ending at UNBOUNDED FOLLOWING.
- PERCENT_RANK and CUME_DIST place each row within its partition as a fraction, handy for percentile-style reporting.
- Aggregates as window functions: SUM, AVG, COUNT, MIN and MAX with OVER and no ORDER BY give a per-partition total on every row, the neatest way to compute a share of total without a self-join.
Performance notes
Window functions sort data by the partition and order columns, which is their main cost.
- Filter first. Reduce rows in a CTE or subquery before the window, not after.
- Support the sort. Microsoft notes that an index whose key columns match the PARTITION BY columns followed by the ORDER BY columns can serve the window without a separate sort.
- Reuse windows. Several functions over the same partition and order share one sort.
- Watch skew in distributed engines. A partition key with one huge group puts that whole group on one worker.
SQL optimisation for data engineers covers reading query plans.
Where these patterns appear in pipelines
- Deduplicating loads and choosing the latest change per key in incremental loads and change data capture.
- Building Type 2 history: LEAD over a change log gives each version's end date.
- Sessionisation and gap detection with LAG, for example starting a new session whenever the gap since the previous event exceeds thirty minutes.
- Spark: the same functions exist in the DataFrame API, as the PySpark tutorial shows with a top-zones-per-borough example.
What is the difference between GROUP BY and a window function?
When should I use ROW_NUMBER instead of RANK?
What does PARTITION BY do?
Why is my running total wrong when dates repeat?
Window functions, ROW_NUMBER, RANK and DENSE_RANK, LEAD and LAG, and PARTITION BY, are a module of the SQL Server course, and SQL for data engineering is part of the Data Engineering course.
Sources
- OVER clause (Transact-SQL) (Microsoft Learn) — Microsoft Learn
- Window functions (SQLite documentation) — SQLite
- Chinook sample database (GitHub) — GitHub
GloryTecks Team
Training & Placement Faculty
The GloryTecks faculty team mentors learners across Data Science, AI and Data Engineering, with a focus on real-time projects and placement readiness in Hyderabad.
Want to master Data Engineering?
Learn SQL Server at GloryTecks in Hyderabad, with trainers who have industry experience, real-time projects and placement assistance.
Related articles
Data Engineering Roadmap 2026: A Complete Guide
A stage-by-stage data engineering roadmap for 2026: what the role involves according to current certification guides, then seven stages, SQL, Python, pipelines and modelling, orchestration and quality, Spark and big data, streaming, and cloud platforms, each with a checkpoint, and detours to avoid.
Data Warehouse Explained: Concepts and Architecture
A clear explanation of data warehouses: why analytical queries need a separate store from transactional systems, the architecture from sources through staging, modelled layers and a semantic model, facts, dimensions, grain and history, modelling approaches, modern platforms and common pitfalls.
Data Lake vs Data Warehouse vs Lakehouse
A clear comparison of data lakes, data warehouses and lakehouses: what each stores and when structure is applied, strengths and failure modes such as the data swamp, what a lakehouse adds through table formats and governance, a side-by-side table, and practical guidance on choosing or combining them.