Each Category's Share of Revenue

The basic idea

A share is a fraction: this row's value over the total of every row you care about. The hard part in SQL is getting the total onto each row, because a plain SUM() collapses rows into one.

In the 2024 rows of category_revenue, Electronics made 600000, Furniture 390000 and Accessories 220000, for a total of 1210000. Electronics' share is 600000 / 1210000, about 49.59 percent.

Why the obvious query fails

Most people first try mixing a column with an aggregate:

SELECT category,
       ROUND(100.0 * total_revenue / SUM(total_revenue), 2)
FROM category_revenue
WHERE year = 2024;

PostgreSQL, SQL Server and MySQL in its default mode reject this, because category isn't grouped. SQLite runs it, but returns a single row: Electronics with 49.59. The aggregate collapsed the table, and the other two categories vanished.

Approach 1: a window function

SUM() OVER () computes the total without collapsing anything:

SELECT category, total_revenue,
       ROUND(100.0 * total_revenue / SUM(total_revenue) OVER (), 2) AS pct_of_total
FROM category_revenue
WHERE year = 2024
ORDER BY pct_of_total DESC;

The empty OVER () means "all rows in the result", and it runs after the WHERE clause, so the total is the 2024 total. This is the shortest version, and a good one to lead with. For more on window functions, see the window functions guide.

Approach 2: a subquery or CTE for the total

Before window functions, the usual way was to compute the total separately and divide by it:

WITH t AS (
  SELECT SUM(total_revenue) AS total
  FROM category_revenue
  WHERE year = 2024
)
SELECT c.category, c.total_revenue,
       ROUND(100.0 * c.total_revenue / t.total, 2) AS pct_of_total
FROM category_revenue c, t
WHERE c.year = 2024
ORDER BY pct_of_total DESC;

It returns the same three rows. Watch the filter, though. The subquery doesn't inherit the outer WHERE clause. Drop WHERE year = 2024 from the CTE and the total becomes both years, 2210000, so the shares come out as 27.15, 17.65 and 9.95. They no longer add up to 100, which is a quick way to spot the bug.

The integer division trap

Counts are integers, and many databases divide integers as integers. The orders table has 15 orders: 11 delivered, 2 pending and 2 shipped.

SELECT status, COUNT(*) AS orders,
       COUNT(*) * 100 / (SELECT COUNT(*) FROM orders) AS pct
FROM orders
GROUP BY status;

This gives 73, 13 and 13. The decimals were thrown away. Start with 100.0 and window over the grouped counts instead:

SELECT status, COUNT(*) AS orders,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM orders
GROUP BY status
ORDER BY pct DESC;

Now it's 73.33, 13.33 and 13.33. Note SUM(COUNT(*)) OVER (): the window runs after GROUP BY, so it sums the group counts. Those rounded values add up to 99.99, not 100. That's normal rounding, and it's worth saying out loud if an interviewer asks why.

Percentage within each group

Add PARTITION BY and the total restarts per group. Here each category's share of its own year:

SELECT year, category, total_revenue,
       ROUND(100.0 * total_revenue / SUM(total_revenue) OVER (PARTITION BY year), 2) AS pct_of_year
FROM category_revenue
ORDER BY year, pct_of_year DESC;

In 2023 the split was exactly 50, 30 and 20. In 2024 Electronics slipped to 49.59 while Furniture grew to 32.23. A related pattern, a share computed from conditions inside one table, is covered in Completion Rate as a Percentage.

What to say in the interview

Lead with the window version and explain that OVER () keeps every row. Mention 100.0 before anyone asks about integer division. If the question has a filter, say where the total comes from, because a total over the wrong rows is the most common wrong answer.