Advanced Analytics with CTEs and Window Functions

Welcome back. Last lesson, you calculated running totals and moving averages from a table that already had one row per region and month. In analytical work, that tidy monthly table is often something you must build from individual orders before a window function can use it.

This lesson brings those steps together. You’ll use common table expressions (CTEs) to turn order records into monthly revenue, apply window functions to that intermediate result, and produce a report for a selected month. The important habit is to make the grain of each step—and the point at which you filter—explicit.

Give each query step one job

A CTE is a named query defined with WITH. Later parts of the same SQL statement can read its result as if it were a table. Multiple CTEs are separated by commas, and a later CTE can read an earlier one.

Watch SQL WITH Clause | Clearly Explained from Maven Analytics for the basic syntax and the reason named steps can be easier to inspect than deeply nested subqueries. The examples use menu items rather than sales, but the query structure is the same.

For our report, the named steps will have distinct purposes:

1. eligible_orders retains orders that count as revenue during the reporting period.
2. monthly_sales combines those orders into one row per region and month.
3. monthly_metrics adds calculations that need to see multiple months.
4. The final SELECT chooses and formats the rows to report.

The CTEs make the logic readable; they do not change what counts as a valid order or month. Those are analytical decisions that the SQL must express.

Aggregate before comparing months

Suppose the question is: For March 2025, what was each region’s revenue, how did it change from February, how much revenue had accumulated since January, and what share of its January–March revenue came from March?

The source is at order grain: one row represents one order. LAG(amount) on that source would retrieve an earlier order, not the prior month’s revenue. We first need a result at region-month grain.

Crunchy Data’s CTEs and Window Functions tutorial shows this same structural move with weekly totals: aggregate observations in one CTE, apply LAG to those totals, then use another CTE when the window result needs further selection.

The difference between order grain and region-month grain is more consequential than the length of the query. Before writing a window function, ask: What does one input row represent at this point?

Build the report in PostgreSQL

The example below is self-contained. Run this setup once in a PostgreSQL session; the temporary table will remain available for that session.

CREATE TEMP TABLE analyst_orders (
    order_id integer PRIMARY KEY,
    region text NOT NULL,
    order_date date NOT NULL,
    amount numeric(12, 2) NOT NULL,
    status text NOT NULL
);

INSERT INTO analyst_orders
    (order_id, region, order_date, amount, status)
VALUES
    (1, 'North', '2025-01-05',  600, 'paid'),
    (2, 'North', '2025-01-20',  400, 'paid'),
    (3, 'North', '2025-02-12', 1200, 'paid'),
    (4, 'North', '2025-03-15',  900, 'paid'),
    (5, 'South', '2025-01-10',  500, 'paid'),
    (6, 'South', '2025-02-09',    0, 'paid'),
    (7, 'South', '2025-03-18',  800, 'paid'),
    (8, 'North', '2025-03-16',  700, 'pending'),
    (9, 'South', '2025-02-11',  100, 'refunded');

For this report, only orders whose current status is paid count as revenue. South’s zero-amount paid order is an observed February row; it is not a missing month. A different business definition of recognized revenue could require different source rules.

Here is the complete query:

WITH eligible_orders AS (
    SELECT
        region,
        order_date,
        amount
    FROM analyst_orders
    WHERE status = 'paid'
      AND order_date >= DATE '2025-01-01'
      AND order_date <  DATE '2025-04-01'
),
monthly_sales AS (
    SELECT
        region,
        DATE_TRUNC('month', order_date)::date AS sales_month,
        SUM(amount) AS revenue
    FROM eligible_orders
    GROUP BY
        region,
        DATE_TRUNC('month', order_date)::date
),
monthly_metrics AS (
    SELECT
        region,
        sales_month,
        revenue,
        LAG(revenue) OVER (
            PARTITION BY region
            ORDER BY sales_month
        ) AS prior_revenue,
        SUM(revenue) OVER (
            PARTITION BY region
            ORDER BY sales_month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_revenue,
        SUM(revenue) OVER (
            PARTITION BY region
        ) AS period_region_total
    FROM monthly_sales
)
SELECT
    region,
    sales_month,
    revenue,
    prior_revenue,
    revenue - prior_revenue AS change_vs_prior,
    running_revenue,
    ROUND(
        100.0 * revenue / NULLIF(period_region_total, 0),
        1
    ) AS pct_of_region_period
FROM monthly_metrics
WHERE sales_month = DATE '2025-03-01'
ORDER BY region;

Read the query from its inputs, not from the final SELECT. eligible_orders has seven rows: the pending and refunded orders are excluded. monthly_sales has six rows, one for each region-month pair. Only then does monthly_metrics compare months within each region. North’s March running revenue, for example, includes its January total of 1,000, February total of 1,200, and March total of 900.

The three window expressions answer different questions. LAG uses the previous row in month order; the ordered, explicitly framed SUM accumulates revenue through the current month; and SUM(revenue) OVER (PARTITION BY region) calculates the region’s whole January–March period total on each of its rows. The final percentage divides March revenue by that period total. NULLIF prevents division by zero if a region’s total is zero, in which case the percentage is NULL.

Why the final filter comes last

It is tempting to move WHERE sales_month = DATE '2025-03-01' into monthly_metrics. That would change the calculation, not merely shorten the display. The window functions in that CTE would then see only March: LAG would have no February row, and the running sum would contain only March revenue.

In the query as written, monthly_metrics calculates values over all three months. The outer WHERE then selects the March results after those values exist. This also gives the final query access to prior_revenue as an ordinary column, so it can calculate change_vs_prior.

The date condition in eligible_orders has a different purpose: it defines the reporting period. Our share metric means “March as a percentage of January–March regional revenue,” not March as a percentage of all historical revenue. If you wanted a March report with a prior-month comparison but began the input period on March 1, you would lose February before LAG could read it. Choose the input period to support every metric you intend to report.

Also retain the distinction from the last two lessons: LAG means the prior available row. Because this sample has a row for every region in January, February, and March, March’s prior row is February. If February were absent, the query would not manufacture it; you would need to establish the appropriate missing-month policy before calling the result a month-over-month change.

Inspect and check each stage

Named steps are useful for debugging because you can inspect an intermediate grain. To inspect monthly_sales, keep the WITH definitions through that CTE and replace the rest of the statement with:

SELECT *
FROM monthly_sales
ORDER BY region, sales_month;

You should find six rows. North’s January revenue should be 1,000 because two orders contribute to that one region-month; South’s February revenue should be zero, not absent.

A separate control query checks the included source records:

SELECT
    COUNT(*) AS eligible_order_count,
    SUM(amount) AS eligible_revenue
FROM analyst_orders
WHERE status = 'paid'
  AND order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-04-01';

It should return 7 orders and 4,400.00 in revenue. The six monthly_sales revenues should sum to the same 4,400.00. If they do not, inspect the eligibility rule, date boundaries, and grouping keys before trusting the window calculations. Once the inputs reconcile, the expected regional period totals are 3,100.00 for North and 1,300.00 for South.

A CTE does not automatically make a query faster or correct; its immediate benefit here is that each transformation has a name, a defined grain, and an output you can inspect.

Key takeaways

A multi-step analytical query often needs to select valid source rows, aggregate to the reporting grain, calculate across those aggregated rows, and only then filter the report. CTEs make those boundaries visible. Window functions preserve the region-month rows while adding prior-month, cumulative, and period-wide metrics; filtering before they run can silently change their answers.

This completes the advanced SQL module. Next, you’ll turn to data quality, starting with standardizing nulls, dates, and text values so that analyses like this one receive consistent inputs.