The underrated power of SQL window functions

There's a moment every analyst has — the moment you discover ROW_NUMBER(), then RANK(), then LAG(), and suddenly the 30-line query you'd been writing for three hours collapses into eight lines that actually do what you wanted. That moment, for me, was the gateway into a part of SQL that most tutorials either skip or rush past: window functions.

This post is for the analyst who knows basic SQL but hasn't spent serious time with window functions yet. My aim is to show you not just how they work, but the kinds of analytical problems they unlock — problems that are genuinely hard to solve elegantly any other way.

What makes window functions different

Regular aggregate functions collapse rows. SUM(revenue) over a group gives you one row per group. That's useful, but sometimes you want the aggregate and the original rows. You want each transaction alongside the running total. You want each user's session alongside their average session length. That's what window functions do — they perform a calculation across a set of related rows while preserving the original row structure.

The syntax has three parts worth understanding:

FUNCTION() OVER (
  PARTITION BY column
  ORDER BY column
  ROWS/RANGE BETWEEN ... AND ...
)

PARTITION BY is like GROUP BY, but without collapsing rows. ORDER BY defines the sequence within each partition. The frame clause (ROWS BETWEEN) defines how far back and forward the window extends. Together, these three give you enormous flexibility.

Running totals and moving averages

The most common window function use case is running totals. If you have daily revenue and want a cumulative sum:

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_revenue

Clean. No self-join. No subquery. The frame defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means "everything up to and including this row."

For a 7-day moving average, you adjust the frame:

SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM daily_revenue

This is far more readable than the equivalent self-join, and in most query engines, it's also faster.

Cohort analysis and retention

Here's where window functions start to shine for product analytics. Cohort retention analysis — figuring out what percentage of users who signed up in week 1 are still active in week 4 — traditionally requires multiple CTEs or subqueries. Window functions make the first step clean.

SELECT
  user_id,
  event_date,
  MIN(event_date) OVER (PARTITION BY user_id) AS first_event_date,
  DATEDIFF(event_date, MIN(event_date) OVER (PARTITION BY user_id)) AS days_since_first
FROM user_events

With days_since_first in hand, you can bucket users into cohorts and compute retention rates with a simple group-by downstream. The window function eliminates the self-join that would otherwise be required to attach first_event_date to every row.

Deduplication with ROW_NUMBER

One of the most practical everyday uses: deduplication. Say you have a table with duplicate records (maybe from an imperfect ETL process), and you want to keep only the most recent version of each record per user:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw_users
)
SELECT * FROM ranked WHERE rn = 1

This pattern — ROW_NUMBER inside a CTE, then filter on rn = 1 — is one of the most versatile in analytical SQL. It works for deduplication, for picking the first/last event per user, for selecting the most recent record per session.

Lead, lag, and period-over-period comparisons

LAG() and LEAD() let you access the previous or next row's value without a self-join. This is powerful for period-over-period comparisons:

SELECT
  week,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY week) AS prev_week_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY week) AS week_over_week_change,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY week)) /
    NULLIF(LAG(revenue, 1) OVER (ORDER BY week), 0) * 100,
    2
  ) AS pct_change
FROM weekly_revenue

Note the NULLIF(..., 0) — essential for avoiding division-by-zero errors when a prior period had zero revenue.

Percentile rank and distribution analysis

PERCENT_RANK() and NTILE() are underused gems for understanding distribution. Want to know which decile each customer falls into by lifetime value?

SELECT
  customer_id,
  lifetime_value,
  NTILE(10) OVER (ORDER BY lifetime_value) AS decile
FROM customer_ltv

Now you can segment your top 10% easily, compare behaviors across deciles, or build targeted campaigns. No complex CASE WHEN with hardcoded thresholds — just a window function.

Gaps and islands

One of the more advanced patterns window functions enable is detecting "gaps and islands" — consecutive sequences and breaks in sequential data. Useful for session analysis, subscription continuity checks, and more. The classic pattern:

SELECT
  user_id,
  activity_date,
  activity_date - ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY activity_date
  ) AS grp
FROM user_activity

Rows that are consecutive get the same grp value. Group by user_id, grp and you can identify contiguous activity streaks. This is genuinely hard to do without window functions.

When not to use them

Window functions have overhead — they require the query engine to materialize the partition and sort it before computing. On very large tables with many partitions, they can be expensive. Profile your queries. Use EXPLAIN. Sometimes a well-indexed lookup table is faster.

Also: not all SQL dialects support all frame types equally well. MySQL's support has historically lagged behind PostgreSQL and BigQuery. Know your environment.

The mental model shift

The real value of learning window functions isn't the functions themselves — it's the way they change how you think about analytical problems. Instead of reaching for a self-join or a correlated subquery, you start asking: "Can I express this as a relationship between a row and its partition?" Usually you can. And when you can, the query is clearer, more maintainable, and often faster.

That mental shift is worth the investment. Start with ROW_NUMBER. Move to LAG. Work your way to frame-aware aggregates. Each one will expand what you can express in a single query — and what you can see in your data.