Totals beside every row with OVER

Window functions calculate across a group of rows but keep every row, so you can compare each one to its group.

This lesson starts the advanced tier. Window functions are the biggest jump between intermediate and advanced SQL, and they come up in almost every analytics interview.

The problem GROUP BY can’t solve

GROUP BY gives one row per group (lesson 12):

SELECT owner_id, SUM(amount) AS rep_total
FROM opportunities
WHERE closed_at IS NULL
GROUP BY owner_id;

That’s 5 rows. The individual deals are gone. But “what share of this rep’s pipeline is this deal?” needs both: each deal and its rep’s total, on the same row.

OVER keeps every row

Add OVER (...) after an aggregate, and it stops collapsing rows. Each row keeps its own values and gets the group’s result beside it:

SELECT name,
       owner_id,
       amount,
       SUM(amount) OVER (PARTITION BY owner_id) AS rep_total
FROM opportunities
WHERE closed_at IS NULL;

All 23 open deals come back. Dev Patel’s three deals each show his $88,000 total.

The set of rows a calculation looks at is called its window. PARTITION BY decides which window each row belongs to.

An empty OVER (): the whole result is one window

Leave the parentheses empty, and every row sees the total of the whole result:

SELECT name,
       amount,
       SUM(amount) OVER () AS total_open,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_open
FROM opportunities
WHERE closed_at IS NULL;

Cedar Expansion’s $150,000 is 14.5% of the $1,032,500 open pipeline. Lesson 18 needed a subquery for percent of total. This does it in one pass.

Several windows in one query

Each window function has its own OVER, so one query can compare a row to several groups:

SELECT name,
       amount,
       COUNT(*)    OVER (PARTITION BY owner_id) AS rep_open_deals,
       AVG(amount) OVER (PARTITION BY owner_id) AS rep_avg,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM opportunities
WHERE closed_at IS NULL;

When window functions run

Window functions run after WHERE, GROUP BY and HAVING, and before ORDER BY. In lesson 3’s run-order table, they slot in between HAVING and SELECT. Two consequences:

1. WHERE decides what’s in the window. Above, the totals only include open deals because WHERE removed the closed ones first.
2. You can’t filter on a window function in WHERE. It doesn’t exist yet when WHERE runs.

To filter on one, calculate it in a CTE (lesson 24), then filter in the outer query. Which open deals make up at least half of their account’s pipeline?

WITH with_totals AS (
    SELECT name,
           amount,
           SUM(amount) OVER (PARTITION BY account_id) AS account_total
    FROM opportunities
    WHERE closed_at IS NULL
)
SELECT name, amount, account_total
FROM with_totals
WHERE amount * 1.0 / account_total >= 0.5;

That returns 7 deals. Two of them are the only open deal at their account, so they’re 100% of it.

GROUP BY vs. OVER

Common mistakes

Dialect notes

Snowflake notes

Snowflake can filter on a window function directly with QUALIFY, which runs after window functions the way HAVING runs after GROUP BY. The CTE above shrinks to one query:

SELECT name, amount,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM opportunities
WHERE closed_at IS NULL
QUALIFY amount / account_total >= 0.5;

QUALIFY is also available in BigQuery and Databricks, but not SQL Server, PostgreSQL, MySQL or SQLite. Run it in the sandbox and you’ll get a syntax error.

More in the field guide: OVER (PARTITION BY …), Aggregates as window functions, the OVER breakdown and QUALIFY.

Try it

Show every open deal with its account’s total open pipeline beside it. Return the deal name, its amount and the account total.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson