How One SQL Keyword Replaces Hours of Spreadsheet Formulas

Hello ,

Last issue we talked 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 a common request: “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.

Or you can write a single SQL query that does all of it.

The Problem: Monthly Snapshots Don’t Show Trends

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

| Month | Revenue |
|——-|———|
| Jan 2025 | $158,413 |
| Feb 2025 | $148,788 |
| Mar 2025 | $208,675 |

Is February’s drop a problem? Is March a recovery or just seasonal? Monthly snapshots can’t answer that. You need context — and that’s what window functions provide.

The Query: Three Trend Metrics in One Pass

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);

Results (2025 data — the high-volume period):

| Month | Bookings | Revenue | Running Total | 3-Mo Avg | MoM Change |
|——-|———-|———|—————|———-|————|
| Jan 2025 | 57 | $158,413 | $595,098 | $115,968 | 33.2% |
| Feb 2025 | 56 | $148,788 | $743,886 | $142,047 | -6.1% |
| Mar 2025 | 94 | $208,675 | $952,561 | $171,959 | 40.2% |
| Apr 2025 | 82 | $204,005 | $1,156,566 | $187,156 | -2.2% |
| May 2025 | 81 | $199,152 | $1,355,718 | $203,944 | -2.4% |
| Jun 2025 | 72 | $152,574 | $1,508,292 | $185,244 | -23.4% |
| Jul 2025 | 51 | $88,308 | $1,596,600 | $146,678 | -42.1% |
| Aug 2025 | 39 | $82,993 | $1,679,593 | $107,958 | -6.0% |
| Sep 2025 | 20 | $50,423 | $1,730,016 | $73,908 | -39.2% |

Now this tells a story. Revenue peaked in March ($208K), plateaued through May ($199K), then declined steadily through summer. The 3-month moving average confirms this wasn’t random noise — it smoothed from $172K in March down to $74K by September.

Breaking Down the Three Window Functions

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(p.amount)` is the GROUP BY aggregate — monthly revenue. The outer `SUM() OVER()` is the window function that accumulates it.

Read it as: “For each month, give me the total revenue from the beginning up to this month.”

This is how you know when you hit $1M in cumulative revenue (between April and May 2025, based on the data).

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` is the window frame — it tells SQL to average this month plus the two months before it.

Why 3 months? It’s a common choice for smoothing. Short enough to track real trends, long enough to filter out one-month spikes. You could use `5 PRECEDING` for a 6-month average if your business has longer cycles.

The moving average tells you what the “normal” level is. When monthly revenue drops below the moving average, the trend is declining. When it’s above, the trend is growing.

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 gives you last month’s revenue, which you can use to calculate percentage change.

That -42.1% drop from June to July 2025? In a monthly snapshot it looks alarming. But the 3-month moving average shows the decline started in June — July continued a trend, it didn’t start one. Context changes the interpretation.

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.

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

3. Scale. This works on 100 rows or 10 million. Spreadsheet performance degrades with volume.

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

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 ([say hi on twitter!](https://twitter.com/briangraves))

—

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](https://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.