SQL Window Functions Don't Have to Be Scary 🪟

Introduction

If you've ever seen OVER (PARTITION BY ... ORDER BY ...) in a SQL query and quietly closed the tab — this post is for you.

Window functions are one of those topics that look intimidating at first but become incredibly powerful once they click. And once you learn them, you'll wonder how you ever wrote SQL without them.

By the end of this post you'll understand 5 essential window functions, when to use each one, and exactly how they differ from each other.

Let's go.

---

What Even Is a Window Function?

A regular aggregate function like SUM() or COUNT() collapses your rows into a single result. Window functions do calculations across rows — without collapsing them.

-- Regular aggregate: collapses all rows into one
SELECT dept, SUM(salary) FROM employees GROUP BY dept;

-- Window function: keeps all rows, adds a calculated column
SELECT name, dept, salary,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

The key difference: GROUP BY gives you one row per group. Window functions give you all your rows back, plus a new calculated column alongside them.

The syntax always follows this pattern:

function_name() OVER (
  PARTITION BY column   -- optional: defines the group
  ORDER BY column       -- optional: defines row order within the group
)

---

Function #1: ROW_NUMBER()

Assigns a unique sequential number to each row within a partition. No ties — every row gets a different number.

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

Result:

name	dept	salary	row_num
Alice	Eng	90k	1
Bob	Eng	85k	2
Carol	HR	70k	1
Dave	HR	65k	2

Even if Alice and Bob had the same salary, they'd still get different row numbers.

When to use it:

• Get the most recent record per user (filter where row_num = 1)
• Deduplicate data
• Paginate query results

---

Function #2: RANK()

Like ROW_NUMBER, but ties get the SAME rank — and the next rank skips.

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

Result:

name	score	rank
Alice	95	1
Bob	95	1
Carol	88	3
Dave	75	4

Notice rank 2 is missing. Alice and Bob both scored 95 so they share rank 1 — and the next rank jumps to 3.

When to use it:

• Ranking students by exam score
• Ranking products by sales volume
• Any scenario where ties are meaningful and gaps are acceptable

---

Function #3: DENSE_RANK()

Like RANK, but NO gaps after ties.

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

Result:

name	score	dense_rank
Alice	95	1
Bob	95	1
Carol	88	2
Dave	75	3

Same data — but now Carol gets rank 2, not rank 3.

RANK vs DENSE_RANK — the simple rule:

• Use RANK() when gaps make sense (sports — if two people tie for gold, no one gets silver)
• Use DENSE_RANK() when gaps look wrong (medal tables, internal employee levels)

---

Function #4: LAG()

Lets you look at the value from the PREVIOUS row.

SELECT
  month, revenue,
  LAG(revenue, 1) OVER (
    ORDER BY month
  ) AS prev_revenue
FROM sales;

Result:

month	revenue	prev_revenue
Jan	10k	NULL
Feb	12k	10k
Mar	11k	12k
Apr	15k	11k

January has NULL for prev_revenue because there's no row before it.

When to use it:

• Month-over-month or year-over-year comparisons
• Calculating the change between consecutive rows
• Detecting gaps or jumps in a time series

---

Function #5: LEAD()

Like LAG — but looks at the NEXT row instead.

SELECT
  month, revenue,
  LEAD(revenue, 1) OVER (
    ORDER BY month
  ) AS next_revenue
FROM sales;

Result:

month	revenue	next_revenue
Jan	10k	12k
Feb	12k	11k
Mar	11k	15k
Apr	15k	NULL

April has NULL because there's no row after it.

---

The Cheat Sheet

Function	Ties	Gaps	Best For
ROW_NUMBER()	Different numbers	N/A	Deduplication, pagination, latest record per group
RANK()	Same rank	Yes	Sports rankings, exams
DENSE_RANK()	Same rank	No	Leaderboards, levels, medal tables
LAG()	N/A	N/A	Compare to previous row
LEAD()	N/A	N/A	Compare to next row

---

One Thing to Remember

All window functions follow the same structure:

function() OVER (
  PARTITION BY ...   -- your "group by" (optional)
  ORDER BY ...       -- row order within the group (optional)
)

PARTITION BY is like GROUP BY — but it doesn't collapse your rows. ORDER BY inside OVER() controls how rows are ordered within each window, independently of the query's own ORDER BY.

---

What's Next?

Once you're comfortable with these 5, explore:

• SUM(), AVG(), COUNT() as window functions
• NTILE() for splitting rows into buckets
• Frame clauses like ROWS BETWEEN for rolling calculations

---

Which window function confused you the most before reading this? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙