How to Write Window Functions for Business Analytics

Most analysts discover window functions the hard way: someone asks for a running total or a ranking, GROUP BY can't do it without a self-join, and the self-join gets ugly fast. Window functions compute across rows without collapsing them — you keep every row in the result while calculating aggregates, ranks, and comparisons against neighboring rows.

The syntax is dense at first, but the skeleton is always the same: an aggregate or navigation function, OVER, then some combination of PARTITION BY (how to group), ORDER BY (how to sort within each group), and an optional frame clause (which rows to include). Six patterns I use constantly, all against realistic tables.

Cumulative revenue

Finance wants "revenue through June," not just "revenue in June." One window function turns monthly totals into a running sum:

SELECT
  date_trunc('month', payment_date) AS month,
  SUM(amount)                       AS monthly_revenue,
  SUM(SUM(amount)) OVER (
    ORDER BY date_trunc('month', payment_date)
  )                                 AS cumulative_revenue
FROM payments
WHERE status = 'succeeded'
  AND payment_date >= '2026-01-01'
GROUP BY date_trunc('month', payment_date)
ORDER BY month;

The double SUM looks odd. The inner SUM(amount) is the GROUP BY aggregate (monthly total). The outer SUM(...) OVER (ORDER BY ...) accumulates those totals row by row. When you omit the frame clause, ORDER BY implies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — everything up to this month. Postgres, MySQL 8+, BigQuery, Snowflake all handle this identically.

Month-over-month growth with LAG

LAG grabs a value from the previous row. Pair it with arithmetic and you have growth rates:

WITH monthly AS (
  SELECT
    date_trunc('month', created_at) AS month,
    SUM(amount)                     AS revenue
  FROM orders
  WHERE created_at >= '2025-07-01'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS growth_pct
FROM monthly
ORDER BY month;

NULLIF on the denominator prevents a divide-by-zero on the first row. Without it, Postgres returns NULL silently, which is easy to overlook in a long result set. Better to be explicit.

For deeper coverage of period comparisons including quarter-over-quarter and year-over-year patterns, see How to Compare Month-over-Month Metrics in SQL.

Ranking customers within segments

Three functions, three behaviors with ties.

ROW_NUMBER() assigns 1, 2, 3, 4 with no ties. Two customers with identical revenue? One gets 3, the other gets 4 — which one is arbitrary unless you add a tiebreaker. RANK() gives both a 2, then skips to 4. DENSE_RANK() gives both a 2, then continues to 3.

This matters when you filter. WHERE row_num <= 3 always returns exactly 3 rows per group. WHERE rnk <= 3 might return fewer if ties push ranks past the cutoff. WHERE dense_rnk <= 3 might return more if every tier has ties.

WITH customer_totals AS (
  SELECT
    c.segment,
    o.customer_id,
    SUM(o.amount) AS total_revenue
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.created_at >= '2026-01-01'
  GROUP BY c.segment, o.customer_id
)
SELECT
  segment,
  customer_id,
  total_revenue,
  ROW_NUMBER() OVER w AS row_num,
  RANK()       OVER w AS rnk,
  DENSE_RANK() OVER w AS dense_rnk
FROM customer_totals
WINDOW w AS (PARTITION BY segment ORDER BY total_revenue DESC)
ORDER BY segment, total_revenue DESC;

I reach for ROW_NUMBER for "top N per group" almost every time because I want a predictable row count. Add the primary key as a tiebreaker — ORDER BY total_revenue DESC, customer_id — to make the ranking deterministic across runs.

Moving averages

Daily revenue bounces around. A 7-day moving average exposes the trend underneath the noise:

SELECT
  order_date,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_7d
FROM (
  SELECT
    DATE(created_at) AS order_date,
    SUM(amount)      AS daily_revenue
  FROM orders
  WHERE created_at >= '2026-01-01'
  GROUP BY DATE(created_at)
) daily
ORDER BY order_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a 7-day window (current row + 6 before). The first 6 rows average over fewer days — filter them out if that matters.

Watch out for missing dates. If nobody orders on a Saturday, that date isn't in the result, and your "7-day" window silently spans 8 or 9 calendar days. Fix this with a date spine — generate_series('2026-01-01'::date, CURRENT_DATE, '1 day') left-joined to your data with COALESCE(daily_revenue, 0). Skip it and your moving averages will be subtly wrong.

FIRST_VALUE, LAST_VALUE, and the frame trap

FIRST_VALUE grabs the first row's value in each partition. Works as expected. LAST_VALUE has a trap that catches everyone at least once: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means LAST_VALUE returns the value from the current row, not the last row in the partition. Useless.

SELECT
  customer_id,
  plan_name,
  changed_at,
  FIRST_VALUE(plan_name) OVER w AS original_plan,
  LAST_VALUE(plan_name)  OVER w AS current_plan
FROM subscription_changes
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY changed_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is the fix. It tells the database to consider the entire partition for every row. Without it, your "current plan" column just mirrors the plan_name column. This is the single most common window function bug I've found in other people's queries.

Putting it all together: customer order profile

Here's a pattern that combines window functions into something genuinely useful. One query, no self-joins — each customer's first order, most recent order, total orders, lifetime revenue, percent of company total, and a revenue ranking:

WITH customer_summary AS (
  SELECT DISTINCT
    customer_id,
    FIRST_VALUE(created_at) OVER w AS first_order_date,
    LAST_VALUE(created_at)  OVER w AS last_order_date,
    COUNT(*)                OVER w AS order_count,
    SUM(amount)             OVER w AS lifetime_revenue
  FROM orders
  WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
)
SELECT
  customer_id,
  first_order_date,
  last_order_date,
  order_count,
  lifetime_revenue,
  ROUND(100.0 * lifetime_revenue / SUM(lifetime_revenue) OVER (), 1) AS pct_of_total,
  DENSE_RANK() OVER (ORDER BY lifetime_revenue DESC) AS revenue_rank
FROM customer_summary
ORDER BY revenue_rank;

The CTE computes per-customer aggregates using a partitioned window. The outer query adds cross-customer calculations: SUM(...) OVER () with an empty OVER clause gives the grand total for percent-of-total, and DENSE_RANK ranks across all customers. Two layers because window functions can't nest inside other window functions. This replaces three or four GROUP BY queries joined back together.

A note on PARTITION BY vs ORDER BY

Quick rule. PARTITION BY defines independent groups, like GROUP BY. ORDER BY defines the sequence within each group, which determines what "preceding" and "following" mean.

Omit PARTITION BY and the entire result set is one partition. Omit ORDER BY and there's no defined sequence — frame-dependent functions like LAG and FIRST_VALUE will give you unpredictable results. If your window function returns the same value for every row, you probably forgot PARTITION BY. If it returns random-looking values, you probably forgot ORDER BY.

Building these queries goes faster when your editor knows the schema. Fastero's SQL editor autocompletes column names inside PARTITION BY and ORDER BY clauses, which cuts the guesswork when you're working against tables with dozens of columns.

Try Fastero free — write window functions against your own data with schema-aware autocomplete and instant results. No credit card required.