A share-of-total column using SUM() OVER () shows shares of a filtered subset, not the company total — why?

## The symptom A dashboard column claims to show each rep's share of company revenue. The finance report says the East region is 55% of the company; the dashboard shows East reps' shares adding to 100%. Nobody's arithmetic is wrong — the two queries are dividing by different denominators. ```sql -- The dashboard query SELECT region, rep, amount, 100.0 * amount / SUM(amount) OVER () AS pct_of_company FROM sales WHERE region = 'EAST'; ``` ## The cause SQL evaluates `WHERE` before window functions. By the time `SUM(amount) OVER ()` runs, the only rows in existence at that query level are East rows, so the window totals East. The column is a correct share-of-East wearing a misleading alias. This is the single most common way an evaluation-order misunderstanding shows up in production: the query is syntactically fine, runs fast, returns numbers of the right magnitude, and is wrong. `HAVING` does the same thing one stage later in a grouped query — any group it removes is absent from a grand total computed by a window. ## Fix 1 — window inside, filter outside Move the filter to a query level *above* the window, so the window still sees the whole table. ```sql SELECT * FROM (SELECT region, rep, amount, 100.0 * amount / SUM(amount) OVER () AS pct_of_company FROM sales) s WHERE region = 'EAST'; ``` Or the same thing as a CTE, which reads better in a long query: ```sql WITH scored AS ( SELECT region, rep, amount, 100.0 * amount / SUM(amount) OVER () AS pct_of_company FROM sales ) SELECT * FROM scored WHERE region = 'EAST'; ``` Now `pct_of_company` means what it says. The cost is honest and stated in the SQL: you have asked for an aggregate over every row of `sales`, so the query reads the whole table even though it returns a handful of rows. ## Fix 2 — compute the denominator separately and cross join When the detail set is large and you only need one scalar, ask for exactly that: ```sql WITH company AS ( SELECT SUM(amount) AS company_total FROM sales ) SELECT s.region, s.rep, s.amount, 100.0 * s.amount / c.company_total AS pct_of_company FROM sales s CROSS JOIN company c WHERE s.region = 'EAST'; ``` `company` is a one-row set, so the `CROSS JOIN` attaches the same scalar to each detail row without changing the row count. A scalar subquery in the select list expresses the same idea. This form also makes the denominator's own scope explicit and independently editable — you can give it a date range that differs from the outer filter, which the wrapping form cannot do. ## What does not fix it - **Widening the frame.** `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` describes which rows *within the partition* the window covers. The partition is drawn from rows the query already holds; a frame cannot resurrect rows `WHERE` discarded. - **Adding or removing `PARTITION BY`.** Partitioning subdivides the surviving rows. Removing it gives you all surviving rows — still only East ones. - **Swapping `WHERE` for `HAVING`.** In a grouped query, `HAVING` still runs before the window, so the removed groups are still missing from the total. It also changes what the query means. ## Choosing between the fixes Wrap-and-filter is the smaller edit and keeps a single row source, so it is the default. Prefer the CTE-plus-`CROSS JOIN` shape when the denominator's scope genuinely differs from the detail rows' scope (company total for the year, detail rows for the month), or when the detail table is large enough that computing a per-row window across all of it is work you would rather not ask for. Whichever you choose, **name the column honestly** — `pct_of_company` versus `pct_of_region` — because the alias is the only thing standing between the next reader and the same misunderstanding. ## Verifying the fix Check the invariant the number must satisfy. If the column claims to be a share of the company, the shares of *all* rows across *all* regions must add to 100, so run the query without the filter and sum it. If it claims to be a share of the region, the shares within one region must add to 100. Sanity checks like this catch the whole family of denominator bugs and cost one extra query. ## What an interviewer is checking That you diagnose from the pipeline rather than by trial and error, that you can name at least two correct rewrites and say when each is preferable, and that you reject the frame-widening "fix" for the right reason.