Data AnalyticsIntermediate16 min readUpdated March 2025

Window Functions & Cohort Analysis

Window functions compute rankings, running totals and period-over-period changes without collapsing rows. Use them with CTEs to build moving averages, top-N reports and retention cohorts.

What is a Window Function?

Unlike `GROUP BY`, a window function calculates over a set of related rows but keeps every row in the output. The `OVER` clause defines the window:

`function() OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)`

Ranking and Top-N per Group

Use `ROW_NUMBER`, `RANK` and `DENSE_RANK` to find the top items in each group — a classic analytics request:

sql

WITH product_sales AS (
    SELECT
        category,
        product_name,
        SUM(revenue) AS revenue
    FROM sales
    GROUP BY category, product_name
),
ranked AS (
    SELECT
        *,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rnk
    FROM product_sales
)
SELECT category, product_name, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk;

Running Totals, Moving Averages and Growth

`SUM() OVER` builds cumulative totals, a frame clause builds moving averages, and `LAG` compares to the previous period:

sql

WITH daily AS (
    SELECT order_date::date AS day, SUM(total_amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    day,
    revenue,
    SUM(revenue) OVER (ORDER BY day)                         AS running_total,
    ROUND(AVG(revenue) OVER (
        ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2)                                                    AS moving_avg_7d,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
          / NULLIF(LAG(revenue) OVER (ORDER BY day), 0), 1) AS pct_change_dod
FROM daily
ORDER BY day;

Monthly Retention Cohorts

A cohort groups users by the month they first purchased, then tracks how many return in later months:

sql

WITH first_order AS (
    SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
activity AS (
    SELECT DISTINCT
        o.customer_id,
        f.cohort_month,
        DATE_TRUNC('month', o.order_date) AS active_month
    FROM orders o
    JOIN first_order f USING (customer_id)
)
SELECT
    cohort_month,
    (EXTRACT(YEAR FROM active_month) - EXTRACT(YEAR FROM cohort_month)) * 12
      + EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month) AS months_since,
    COUNT(DISTINCT customer_id) AS active_customers
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;

Key Takeaways

Window functions keep row-level detail while aggregating.
PARTITION BY defines groups; ORDER BY defines sequence.
LAG/LEAD enable period-over-period comparisons.
CTEs make multi-step analyses like cohorts readable.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com