Write a query to compute the percentage of total revenue contributed by each product category.

Answer

Divide each category's revenue by the total revenue across all categories, computed with a window function so the grand total is available on every row.

Solution

SELECT category,
       SUM(revenue) AS category_revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) AS pct_of_total
FROM Sales
GROUP BY category;

Step by Step

1. SUM(revenue) grouped by category gives each category's own total revenue.
2. SUM(SUM(revenue)) OVER () - a window function applied over an aggregate - computes the grand total across all categories, repeated on every output row (the empty OVER () means no partitioning, so it spans the whole result set).
3. Dividing the category total by the grand total, multiplied by 100, gives the percentage contribution.

Alternative Method: Subquery for the Grand Total

SELECT category,
       SUM(revenue) AS category_revenue,
       ROUND(100.0 * SUM(revenue) / (SELECT SUM(revenue) FROM Sales), 2) AS pct_of_total
FROM Sales
GROUP BY category;

This version computes the grand total once via a scalar subquery, entirely independent of the outer GROUP BY - functionally identical, and arguably easier to read for someone less familiar with nesting an aggregate inside a window function.

Key Exam Traps

• Integer division: Writing 100 * SUM(revenue) / SUM(...) without at least one decimal literal (like 100.0) can silently truncate to integer division on some databases, producing 0% for every small category. Always force floating-point division explicitly.
• SUM(SUM(...)) looks unusual but is valid: the inner SUM(revenue) is the grouped aggregate per category, and the outer SUM(...) OVER () re-aggregates those already-grouped values across the whole result - a nested aggregate window, not a syntax error.
• Rounding before vs after division changes precision - round only at the very end, never on intermediate values, to avoid compounding rounding errors across categories that should sum to exactly 100%.