Jesse Ngugi

Posted on Sep 5

SQL window functions I wish I knew earlier

#sql #datascience #windowfunctions #dataanalysis

Jun 2026

A no-fluff guide to LAG, LEAD, ROW_NUMBER, and the patterns that make complex analytical queries readable and fast.

For years I solved ranking, running totals, and “previous row” problems with self-joins, correlated subqueries, or pulling data into pandas. The queries were long, slow, and hard to read. Then I properly learned window functions. Overnight, whole classes of problems became three-line queries that were both clearer and faster.

Here’s the practical subset I wish someone had shown me on day one.

The 30-second mental model

A window function calculates a value for every row using a “window” of related rows, without collapsing the result set the way GROUP BY does.

Basic shape:

function_name(...) OVER (
  PARTITION BY some_columns   -- optional: restart the window for each group
  ORDER BY some_columns       -- required for ranking / lag / lead / running totals
  ROWS BETWEEN ...            -- optional: control the exact frame
)

PARTITION BY is like a GROUP BY that doesn’t reduce rows.

ORDER BY defines the sequence inside each partition.

1. ROW_NUMBER, RANK, DENSE_RANK — ranking inside groups

Use when: you need the top N per category, or to deduplicate.

SELECT
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC)     AS rnk,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC)     AS dense_rnk
FROM orders;

customer_id	order_date	amount	rn	rnk	dense_rnk
A	2026-05-10	200	1	1	1
A	2026-04-02	150	2	2	2
A	2026-03-15	150	3	2	2
B	2026-05-01	300	1	1	1

ROW_NUMBER() — unique sequential number (no ties).
RANK() — leaves gaps after ties.
DENSE_RANK() — no gaps.

Classic pattern — latest order per customer:

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

2. LAG and LEAD — look at the previous / next row

Use when: month-over-month change, “time since last event”, detecting status changes.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
  LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount,
  amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS delta
FROM orders;

customer_id	order_date	amount	prev_amount	next_amount	delta
A	2026-03-15	150	NULL	150	NULL
A	2026-04-02	150	150	200	0
A	2026-05-10	200	150	NULL	50

Practical pattern — days since previous order:

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last
FROM orders;

3. Running totals and moving averages

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total,
  AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7
FROM daily_sales;

The frame clause (ROWS BETWEEN ...) lets you control exactly which rows are included. Defaults are usually “everything from the start of the partition up to the current row” when you have an ORDER BY.

4. Patterns that replace ugly self-joins

Month-over-month growth

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) 
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly_revenue;

First and last value in a group

FIRST_VALUE(status) OVER (PARTITION BY customer_id ORDER BY event_time) AS first_status,
LAST_VALUE(status)  OVER (PARTITION BY customer_id ORDER BY event_time
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_status

Gaps and islands (consecutive sequences)

A common technique uses ROW_NUMBER() to create a grouping key for consecutive dates or statuses. Once you see it once, you start spotting it everywhere.

Performance notes (the part most tutorials skip)

Window functions are usually faster than equivalent self-joins because the engine can compute them in a single pass.
Index the columns you PARTITION BY and ORDER BY.
Avoid unnecessary ORDER BY inside the window if you don’t need a sequence (e.g., a plain running sum across the whole table can sometimes skip it).
In modern engines (Postgres, DuckDB, BigQuery, Snowflake, Redshift) the optimizer is quite good; still, test with EXPLAIN on large tables.
Prefer ROWS over RANGE unless you specifically need value-based framing (RANGE can be slower).

Cheat-sheet of the functions you’ll use 90% of the time

Function	Purpose	Needs ORDER BY?
ROW_NUMBER()	Unique ranking	Yes
RANK()	Ranking with gaps	Yes
DENSE_RANK()	Ranking without gaps	Yes
LAG(col, n)	Value from n rows before	Yes
LEAD(col, n)	Value from n rows after	Yes
SUM / AVG / COUNT	Running or partitioned aggregates	Optional
FIRST_VALUE / LAST_VALUE	Edge values in the window	Yes
NTILE(k)	Split into k roughly equal buckets	Yes

Final thought

Window functions turn “I need to compare each row to other rows in its group” from a multi-join nightmare into readable, declarative SQL. Once they click, you’ll start rewriting old queries just for the clarity.

Master ROW_NUMBER, LAG/LEAD, and the running-total pattern. Those three cover the majority of real analytical work. Everything else is variations on the same idea.

Your future self (and anyone who has to maintain your queries) will thank you.

DEV Community