The Window Function Trick That Saved Me a CTE

The question I wanted answered

In the project ontario-energy-mix, I had a table of monthly electricity generation by fuel type, plus peak demand by month. The question: is Gas's share of total generation disproportionately higher in the months when peak demand is highest? To answer it I needed, for each month category, both the generation from Gas and the generation from everything else, plus what percentage each represented of that category's total.

Where I started

Ranking months by peak demand and labelling the top 10 was straightforward:

WITH month_ranks AS (
    SELECT
        month,
        DENSE_RANK() OVER(ORDER BY peak_demand_gw DESC) AS rnk
    FROM demand_matching_generation_range
),
peak_months AS (
    SELECT
        month,
        CASE WHEN rnk <= 10 THEN 'Top 10 Months' ELSE 'Other Months' END AS month_category
    FROM month_ranks
),

Then, I assumed that I would need two additional CTEs to arrive at the final result I needed, specifically the percentage share of gas and other fuels in the total. My instinct was: aggregate generation by month category and fuel category first, then wrap that result in a second CTE that sums on just the month category to get the denominator for a percentage. Two more CTEs, just to get one number.

The trick: aggregating inside a window function

Window functions run after GROUP BY, which means the value flowing into them is already a per-group aggregate. Nothing stops you from wrapping that aggregate in another aggregate function and windowing over it in the same SELECT:

generation_total AS (
    SELECT
        pm.month_category,
        CASE WHEN g.fuel = 'GAS' THEN 'Gas' ELSE 'Others' END AS fuel_category,
        SUM(g.output_gwh) AS fuel_category_output,
        SUM(SUM(g.output_gwh)) OVER(PARTITION BY pm.month_category) AS month_category_total
    FROM generation g
    JOIN peak_months pm ON g.month = pm.month
    GROUP BY
        pm.month_category,
        CASE WHEN g.fuel = 'GAS' THEN 'Gas' ELSE 'Others' END
)

The inner SUM(output_gwh) produces the row-level total for each (month_category, fuel_category) pair, same as any grouped aggregate. The outer SUM(...) OVER(PARTITION BY month_category) then adds those already-aggregated rows back up within each month category — a window-of-aggregate, computed in the same pass instead of a separate rollup query.

From there, fuel_category_output * 100.0 / month_category_total gives the percentage directly, no extra join or CTE required.

SELECT
    *,
    ROUND(fuel_category_output * 100.0 / month_category_total, 2) AS percentage_of_total_generation
FROM generation_total
ORDER BY month_category DESC, fuel_category ASC

The takeaway

SQL has a fixed logical processing order:
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT (window functions evaluated here) → ORDER BY

It is clear from the flow above that since SELECT is evaluated after GROUP BY, what window functions get are not raw row values in the original table, but values that have been aggregated by GROUP BY. As much as I knew SQL's flow as a fact, it never occurred to me until recently that window functions can actually operate on aggregated values.

When you are in the thick of it, the basics can easily get overshadowed by the 10 other things on your mind. But, the basics are the most important.

From now on, any time I catch myself about to write a CTE whose only job is "sum this again for a group total," that's the signal to check whether a window-of-aggregate does it in the same SELECT instead.