By Prakhar Shrivastava · October 17, 2026

A WHERE filter in the same query removes rows before the window calculation. If you keep only the US row and then calculate SUM(revenue_usd) OVER (), the denominator contains only US revenue. Calculate the share over the intended population in an inner query, then apply the display filter outside it.

Define what “share” means

Consider a fictional complete-month report with three market totals: US $600, UK $300, and Germany $100. All amounts are already in USD under the reporting policy. There is one row per market, no missing amount, and no duplicate market row. Total revenue is $1,000, so the US contributes 60%.

The dashboard request is “Show the US contribution to these three markets combined.” It is not “Show the US contribution to US revenue.” Both questions have valid answers, but they require different denominators. Name the population in the metric label, especially when a filter control can change what is visible.

The query that returns an unexpected 100%

WITH sales(market, revenue_usd) AS (
  VALUES ('US',600), ('UK',300), ('DE',100)
)
SELECT market,
       100.0 * revenue_usd / SUM(revenue_usd) OVER () AS share_pct
FROM sales
WHERE market = 'US';
-- US | 100.0

The arithmetic is consistent with the filtered input: $600 divided by $600 equals 100%. There is no evidence of increased sales. The bug is a mismatch between the business question and the query’s population. Adding an unbounded frame cannot restore rows that the query already removed.

Calculate first, display second

WITH sales(market, revenue_usd) AS (
  VALUES ('US',600), ('UK',300), ('DE',100)
), shares AS (
  SELECT market, revenue_usd,
         SUM(revenue_usd) OVER () AS total_usd
  FROM sales
)
SELECT market,
       100.0 * revenue_usd / NULLIF(total_usd, 0) AS share_pct
FROM shares
WHERE market = 'US';
-- US | 60.0

The inner query attaches the $1,000 total to every market row. The outer query chooses what to show. The factor 100.0 makes the intended percentage calculation explicit in this integer-valued fixture. NULLIF makes a zero denominator return NULL rather than a fabricated zero percent.

Change only the outer filter to WHERE market IN ('UK','DE') and the returned shares are 30% and 10%. They add to 40%, which is correct for this displayed subset of the original population. Renormalizing visible rows to 100% would answer a different question: within the UK-and-Germany subset, the shares would be 75% and 25%.

Which filters belong inside?

Filters that define the measurement population belong before the denominator. For example, if the metric covers completed orders in September, apply that date and status rule before calculating both numerator and total. Filters that merely choose which results to display belong after the calculation when the denominator must remain fixed.

Suppose the source contains several months. A total over all rows would combine months. First establish the intended reporting grain and date range; if comparing market shares separately per month, use a month-specific denominator such as SUM(revenue_usd) OVER (PARTITION BY reporting_month) on a table already aggregated to month and market. An outer date filter is appropriate only if the inner calculation already defines each month’s population correctly.

Check the input before interpreting the percentage

A repeated market row can inflate the total. Missing amounts need an explicit policy: ignoring an unknown amount does not make the resulting total complete. Refunds or adjustments may produce negative revenue or shares outside the usual zero-to-100 range. A zero net total is undefined for this calculation, even if positive and negative activity occurred.

The fixture with all three amounts changed to zero returns NULL for the US share. Preserve that distinction in the dashboard; replacing it with 0% would imply a known share of a valid total. See the NULL versus zero guide for related reporting decisions.

Verify the denominator as well as the output

Four result assertions passed in SQLite 3.50.4: the early-filter query returns 100%, the outer-filter query returns 60%, the UK/Germany display returns 30% and 10%, and a zero-total fixture returns NULL. Test the same cases in your deployed database and inspect the denominator column during review.

The official PostgreSQL window tutorial explains which filtered rows a window can see and how an outer query can filter calculated results. Continue with the window-functions reference, integer-division example, and relative rank versus cumulative share. A reliable percentage requires a documented numerator, denominator, and filter scope.