Hello Reader,

A while back, I shared some tips about formatting SQL output for spreadsheets. Today we're going the other direction. Doing the kind of analysis that makes spreadsheet formulas feel clunky.

Here's the kind of request that analysts get every single day: "Show me monthly revenue and how the trend is moving."

If you pull monthly revenue into a spreadsheet, you'd probably create a running total column, then a 3-month moving average column, then a month-over-month change column. That's three extra calculated columns you have to build and maintain every time.

It sucks.

Instead, you should write a single SQL query that does all of it. Here's how.

Show Trends (Not Just Monthly Snapshots)

Here's raw monthly revenue. Useful, but incomplete:

Is February's drop a problem?

Is March a recovery or just seasonal?

Monthly snapshots can't answer that. You need to provide context.

Fortunately, that's what window functions provide.

Three Trends, One SQL Query

Here's what I ran against the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics):

SELECT 
    TO_CHAR(DATE_TRUNC('month', b.booking_date), 'Mon YYYY') AS month,
    COUNT(DISTINCT b.booking_id) AS bookings,
    '$' || TO_CHAR(SUM(p.amount)::numeric, 'FM999,999') AS revenue,
    -- Running total: cumulative revenue over time
    '$' || TO_CHAR(
        SUM(SUM(p.amount)) OVER (
            ORDER BY DATE_TRUNC('month', b.booking_date)
        )::numeric, 'FM999,999'
    ) AS running_total,
    -- 3-month moving average: smooths out spikes
    '$' || TO_CHAR(
        ROUND(AVG(SUM(p.amount)) OVER (
            ORDER BY DATE_TRUNC('month', b.booking_date) 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )::numeric, 0), 'FM999,999'
    ) AS three_month_avg,
    -- Month-over-month change
    COALESCE(
        ROUND(
            (SUM(p.amount) - LAG(SUM(p.amount)) OVER (
                ORDER BY DATE_TRUNC('month', b.booking_date)
            )) / LAG(SUM(p.amount)) OVER (
                ORDER BY DATE_TRUNC('month', b.booking_date)
            ) * 100
        , 1)::text || '%', 
        '—'
    ) AS mom_change
FROM bookings b
    INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
    AND b.status IN ('completed', 'confirmed')
GROUP BY DATE_TRUNC('month', b.booking_date)
ORDER BY DATE_TRUNC('month', b.booking_date);

The trend is way clearer now. Sales momentum built throughout the year. Revenue grew steadily, with only a brief dip in October before finishing the year at a record $119K in December.

The 3-month moving average tells the same story. It climbed from $1K to nearly $79K by year-end. So it's sustained growth, not peaks and valleys.

Demystifying SQL Window Functions

Window Function #1: SUM() OVER ("Running Total")

SUM(SUM(p.amount)) OVER (
    ORDER BY DATE_TRUNC('month', b.booking_date)
)

Yes, that's SUM() wrapping another SUM(). The inner SUM() calculates each month's revenue. The outer SUM() OVER() accumulates those monthly totals.

Read it like this: "Show me total revenue earned up to this month."

This is one of the simplest ways to measure business progress. Instead of looking at isolated months, you can see revenue compound over time from under $1K to more than $436K by year-end.

Window Function #2: AVG() OVER with ROWS BETWEEN ("Moving Average")

AVG(SUM(p.amount)) OVER (
    ORDER BY DATE_TRUNC('month', b.booking_date)
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW tells SQL to average the current month and the previous two months.

A 3-month moving average smooths out month-to-month noise so the underlying trend becomes more clear. Here it rises almost continuously throughout the year (even when October dips) showing that the business was still growing overall.

Very powerful!

Window Function #3 LAG() ("Month-Over-Month Change)

LAG(SUM(p.amount)) OVER (
    ORDER BY DATE_TRUNC('month', b.booking_date)
)

LAG() looks back one row. Here it returns last month's revenue so you can calculate month-over-month growth.

Month-over-month changes explain what happened from one month to the next, but they rarely tell the whole story. October's 44.5% decline looks significant on its own, yet the moving average continued climbing and revenue rebounded immediately afterward.

That's why analysts rarely rely on MoM change in isolation.

Why This Beats Spreadsheets

Three reasons:

1) Reproducibility. This query runs on any date range without manual updates. In a spreadsheet, adding a new month means extending formulas.

Once you learn how SQL window functions work you will save time.

2) Consistency. Everyone who runs this query gets the same calculation. Spreadsheet formulas can be accidentally overwritten or applied inconsistently.

You are moving the business logic further up the data pipeline.

3) Scale. This works on 100 rows or 10 million. In your spreadsheet, you'll face slow loading times and processing times if you have too many rows.

This is also where dashboards excel.

Tools like Tableau or Power BI build running totals and moving averages visually, but they need the underlying data to be structured correctly. When you know the SQL mechanics, you understand what the dashboard is doing.

That understanding makes you a better analyst in any tool.

Common Mistakes to Avoid with SQL Window FUnctions

Mistake 1: Forgetting that window functions don’t reduce rows

Unlike GROUP BY, window functions keep every row. If you GROUP BY month and then add a window function, you get one row per month with the window calculation added. If you forget GROUP BY, you get one row per booking with window calculations. Probably not what you want.

Mistake 2: Confusing ROWS and RANGE

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW counts exactly 2 rows back.

RANGE BETWEEN considers values, not row positions.

For time series, ROWS is usually what you want. It’s more predictable when months have gaps.

Mistake 3: Using window functions in WHERE clauses

This won’t work:

WHERE SUM(amount) OVER (…) > 100000 – ERROR 

Window functions run after WHERE. If you need to filter on a window function result, wrap the query in a subquery or CTE first.

Try This Today

Take any monthly report you already run and add one new column:

- Add this to any GROUP BY month query
SUM(your_metric) OVER (
    ORDER BY your_month_column
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) / 3 AS three_month_avg

The first time you show a manager a moving average alongside monthly numbers, they'll ask better questions.

Instead of "why did revenue drop in July?" they'll ask "when did the downward trend start?"

And that's a much more useful conversation.

Until next time,

Brian

Brian Graves, creator of Analytics in Action Say 👋 on X/Twitter, LinkedIn, or book a call with me. You can always reply to these emails. I check them all.

​

P.S. Window functions are one of those skills that separate "I can write SQL" from "I can do analysis." They're covered in depth in SQL for Business Impact, alongside real-world scenarios where you'd use them. Check it out at sqlforbusinessimpact.com.

P.P.S. What's the trickiest spreadsheet calculation you've ever had to maintain? Reply and tell me. I'm curious whether SQL could simplify it. I read every response.