SQL Window Functions: Querying Multiple Aggregation Levels at Once

One of the first things you run into with GROUP BY is that it’s destructive. The moment you aggregate, you lose your individual rows. You can see the campaign total, or you can see each daily row, but not both at the same time. If you want both, you reach for a subquery or a CTE to join the aggregated result back to the detail.

Window functions solve this differently. They compute an aggregate across a set of rows, but then hand the result back to each individual row. Nothing collapses. The row you started with is still there, now carrying the group total, the grand total, or any other cross-row calculation you need.

That’s what makes it possible to compute multiple aggregation levels in a single query. It’s one of the most immediately useful things you can learn about window functions.

The examples in this article use a campaign_performance table with this shape. All queries are scoped to March 2026.

-- campaign_name | channel    | funnel_stage | report_date | impressions | clicks | conversions | spend
-- ──────────────┼────────────┼──────────────┼─────────────┼─────────────┼────────┼─────────────┼──────
-- spring_sale   | google_ads | conversion   | 2024-03-01  | 12000       | 840    | 42          | 320.00
-- spring_sale   | meta_ads   | conversion   | 2024-03-01  | 18500       | 555    | 28          | 280.00
-- brand_aware   | google_ads | awareness    | 2024-03-01  | 45000       | 900    | 5           | 150.00
-- ...

The result snippets below use these six rows scoped to March 2026. Six rows is enough to see every pattern in the output without noise getting in the way:

The Core Idea: PARTITION BY Controls the “Grain”

The PARTITION BY clause in a window function defines which rows contribute to each calculation. Change the partition, and you change the level of aggregation (without changing anything else about the query).

SELECT
    campaign_name,
    channel,
    report_date,
    -- Level 1: the row itself (no window function needed)
    spend AS daily_spend,
    -- Level 2: total spend for this campaign across all channels and dates
    SUM(spend) OVER (PARTITION BY campaign_name) AS campaign_total_spend,
    -- Level 3: total spend across everything in the result set
    SUM(spend) OVER () AS grand_total_spend
FROM campaign_performance
WHERE report_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY campaign_name, channel, report_date;

The difference between these three columns is entirely in the PARTITION BY:

• No window function → the row’s own value
• PARTITION BY campaign_name → one total per campaign, repeated on every row of that campaign
• OVER () with no partition → one total across the entire result set, repeated on every row

None of these collapse the data. Every row from campaign_performance still appears in the output.

Why This Is Useful: Percent of Total

Once you have multiple aggregation levels on the same row, calculations that previously required a subquery become a single expression.

Percent of campaign total:

SELECT
    campaign_name,
    channel,
    report_date,
    spend,
    SUM(spend) OVER (PARTITION BY campaign_name) AS campaign_total_spend,
    ROUND(
        spend * 100.0 / NULLIF(SUM(spend) OVER (PARTITION BY campaign_name), 0),
    2) AS day_pct_of_campaign_spend
FROM campaign_performance
WHERE report_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY campaign_name, day_pct_of_campaign_spend DESC;

Percent of monthly total:

SELECT
    campaign_name,
    channel,
    report_date,
    spend,
    SUM(spend) OVER (
        PARTITION BY DATE_TRUNC('month', report_date)
    ) AS month_total_spend,
    ROUND(
        spend * 100.0 / NULLIF(SUM(spend) OVER (
            PARTITION BY DATE_TRUNC('month', report_date)
        ), 0),
    2) AS day_pct_of_month_spend
FROM campaign_performance
WHERE report_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY report_date, day_pct_of_month_spend DESC;

Note that this partition groups all campaigns together within each month. Each row’s percentage is its share of total spend across every campaign that month, not just its own campaign.

Without window functions, both of these would require a subquery like SELECT SUM(spend) FROM campaign_performance joined or cross-joined back to the main query. The window version does the same thing in one pass, inline.

Stacking All Three Levels

The real payoff is when you need all three levels at once: row detail, group subtotal, and grand total, to build a report that makes sense at a glance.

SELECT
    campaign_name,
    channel,
    report_date,
    spend,
    SUM(spend) OVER (
        PARTITION BY campaign_name, channel
    ) AS channel_total_spend,
    SUM(spend) OVER (
        PARTITION BY campaign_name
    ) AS campaign_total_spend,
    SUM(spend) OVER () AS grand_total_spend,
    -- What share of the campaign's budget went to this specific channel?
    ROUND(
        SUM(spend) OVER (PARTITION BY campaign_name, channel) * 100.0
        / NULLIF(SUM(spend) OVER (PARTITION BY campaign_name), 0),
    1) AS channel_pct_of_campaign_spend,
    -- What share of total budget does this channel represent?
    ROUND(
        SUM(spend) OVER (PARTITION BY campaign_name, channel) * 100.0
        / NULLIF(SUM(spend) OVER (), 0),
    1) AS channel_pct_of_grand_total_spend
FROM campaign_performance
WHERE report_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY campaign_name, channel, report_date;

One query, six different numbers per row: row spend, channel total, campaign total, grand total, and two ratios. No joins or subqueries needed.

A Practical Pattern: Contribution Analysis

One of the most common reporting needs in marketing analytics is understanding which channels, campaigns, or segments are driving results. Window functions make this a one-step operation.

WITH campaign_summary AS (
    SELECT
        campaign_name,
        channel,
        SUM(spend)        AS channel_total_spend,
        SUM(conversions)  AS channel_total_conversions,
        ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS channel_cpa
    FROM campaign_performance
    WHERE report_date BETWEEN '2026-03-01' AND '2026-03-31'
    GROUP BY campaign_name, channel
)

SELECT
    campaign_name,
    channel,
    channel_total_spend,
    channel_total_conversions,
    channel_cpa,
    -- What % of this campaign's spend is on this channel?
    ROUND(
        channel_total_spend * 100.0
        / NULLIF(SUM(channel_total_spend) OVER (PARTITION BY campaign_name), 0),
    1) AS channel_pct_of_campaign_spend,
    -- How does this channel's CPA compare to the campaign average CPA?
    ROUND(
        channel_cpa - AVG(channel_cpa) OVER (PARTITION BY campaign_name),
    2) AS channel_cpa_vs_campaign_avg,
    -- Rank channels within each campaign by conversions
    RANK() OVER (
        PARTITION BY campaign_name
        ORDER BY channel_total_conversions DESC
    ) AS channel_rank_in_campaign
FROM campaign_summary
ORDER BY campaign_name, channel_rank_in_campaign;

This is the query you want when a stakeholder asks: Which channels are working best? And are we spending proportionally? You can answer all three implicit questions in one result set: absolute performance, share of budget, and relative efficiency.

Things Worth Knowing

The OVER () empty partition is the full result set, not the full table. If your query has a WHERE clause, OVER () totals only the rows that passed the filter. That is why scoping with a WHERE on the date is the right place to define your analysis window: the window functions then operate within that scope automatically. It can still surprise you if you expect OVER () to reflect raw table totals across all time.

Window functions run after WHERE and after GROUP BY, but before HAVING and ORDER BY. This means you can’t filter on a window function result in a WHERE clause. If you need to, wrap the whole query in a CTE and filter in the outer query.

PARTITION BY and GROUP BY are not the same. GROUP BY collapses rows. PARTITION BY defines a scope for the window calculation but leaves every row intact. A query can have both: a GROUP BY to aggregate to some intermediate grain, and then window functions on top of that aggregated result.

Repeated window expressions are fine to write; refactoring them into a CTE is fine too. There’s no strict rule. If the same SUM(...) OVER (PARTITION BY ...) appears three times in your SELECT list, it’s readable, but a CTE that computes it once and names it is often cleaner at scale.

Cheat Sheet

The underlying idea is simple: PARTITION BY sets the boundary of each calculation, and OVER () means no boundary. Stack as many of these as you need in one query, each at a different grain, each returning its result back to the original row.

If you have used GROUP BY for years and never touched window functions, this is a good place to start. Pick one query you run regularly that joins back an aggregate, and rewrite it with a window function. The structure becomes clear fast once you see it with your own data.