# Cost Share Within Category

> Each entry's slice of the category total.

Canonical URL: <https://datadriven.io/problems/cost_share_within_category>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The FinOps team wants to see each allocation's weight within its spending category, but only for compute, storage, and network. For each entry in those categories, show the allocation ID, category, amount, and what percentage of that category's total it represents. List by category then allocation ID.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests window functions for computing a percentage of a group total. The `SUM() OVER (PARTITION BY)` pattern is fundamental to analytics queries and appears frequently in interviews.

---

### Break down the requirements

#### Step 1: Filter to target categories

`WHERE category IN ('compute', 'storage', 'network')` restricts to the three specified categories.

#### Step 2: Compute percentage within category

`100.0 * amount / SUM(amount) OVER (PARTITION BY category)` gives each allocation's share of its category total.

#### Step 3: Sort as specified

`ORDER BY category, alloc_id` for the output ordering.

---

### The solution

**Window function for within-group percentage**

```sql
SELECT
    alloc_id,
    category,
    amount,
    ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY category), 2) AS pct_of_category
FROM cost_allocs
WHERE category IN ('compute', 'storage', 'network')
ORDER BY category, alloc_id
```

> **Cost Analysis**
>
> Scan of 15M rows filtered to 3 categories (reducing to perhaps 5M). The window function computes a running sum per partition. No GROUP BY needed since we output every row.

> **Interviewers Watch For**
>
> Whether the candidate uses a window function vs a self-join or correlated subquery for the category total. The window function approach is the idiomatic and most efficient pattern.

> **Common Pitfall**
>
> If any category has zero total amount, division by zero occurs. Use `NULLIF(SUM(amount) OVER (...), 0)` to guard against this edge case.

---

## Common follow-up questions

- What if you also needed a cumulative percentage within each category? _(Tests SUM() OVER (PARTITION BY category ORDER BY alloc_id) for running totals.)_
- How would you find allocations that represent more than 10% of their category? _(Filter on the computed percentage in a CTE or subquery.)_
- What is the difference between PARTITION BY and GROUP BY here? _(PARTITION BY preserves individual rows; GROUP BY collapses them. Tests fundamental window function understanding.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/cost_share_within_category)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.