# Total Cost by Category

> Total spend per category.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The FinOps team needs category-level totals for the annual budget review. For each cost allocation category, return the sum of all amounts.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a basic GROUP BY aggregation. Interviewers use it as a warm-up to confirm you can write a clean aggregate query and choose the right grouping column.

---

### Break down the requirements

#### Step 1: Group by category

`GROUP BY category` partitions the 10M rows in `cost_allocs` into 8 groups (one per category).

#### Step 2: Sum amounts

`SUM(amount)` computes the total allocation for each category.

---

### The solution

**Straightforward group-and-sum**

```sql
SELECT category, SUM(amount) AS total_amount
FROM cost_allocs
GROUP BY category
```

> **Cost Analysis**
>
> Full scan of 10M rows. With only 8 distinct categories, the hash aggregation is extremely efficient. No index is needed for such a low-cardinality grouping.

> **Interviewers Watch For**
>
> Unnecessarily adding ORDER BY when the prompt does not specify sort order. Clean, minimal queries are preferred.

> **Common Pitfall**
>
> Using `COUNT(amount)` instead of `SUM(amount)`. COUNT returns the number of rows, not the total of the values.

---

## Common follow-up questions

- How would you add a percentage-of-total column? _(Tests window function: `SUM(amount) / SUM(SUM(amount)) OVER ()`.)_
- What if some amount values were NULL? _(SUM ignores NULLs, but COUNT(*) vs COUNT(amount) would differ.)_
- How would you filter to only categories with total above a threshold? _(Tests HAVING vs WHERE: the filter is on an aggregate, so HAVING is required.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/total_cost_by_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.