Total Cost by Category

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Siemens

Problem

Every allocation in cost_allocs counts, across all of its periods, and the negative amounts are credits that net against spend rather than being dropped. Give the FinOps team's budget review the total amount per cost category.

Total spend per category.

Tables

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

Worked solution and explanation

Strip off the FinOps wording and this is a one-key rollup of a ledger: six cost categories, one total each, every row landing in exactly one bucket. Nobody fails it for lack of syntax. People fail it by answering a different question. One version is a grand total over cost_allocs that never mentions category. The other is a bare category next to SUM(amount) with nothing bucketing by it, which a permissive engine runs happily, returning one row: the whole table's spend glued to whichever category it read first. The second is the dangerous one, because nothing errors and the review books every dollar to one bucket.

Getting to the query

Step 1: Read the grain off the output

The answer has one row per category, so category is the key every row is bucketed by. The seed holds six values ('compute', 'storage', 'network', 'database', 'ml', 'other') across 200 rows, and the preview already shows that one-row-per-category shape. Read the grain from the shape before writing a clause.

Step 2: Total amount inside each bucket, signs and all

SUM(amount) per group, aliased total_amount so the column says what it holds. Every row keeps its sign: alloc_id 216 is a credit of -120.5 booked to 'ml', and it nets against that category's spend instead of being filtered away. No period filter either: the review covers every period in the table.

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

Common follow-up questions

• How would you add each category's share of total spend? (Tests a window over the grouped result: `SUM(amount) / SUM(SUM(amount)) OVER ()`.)
• What if some `amount` values were `NULL`? (`SUM` skips them; it matters once you also report `COUNT(*)` versus `COUNT(amount)`.)
• How would you keep only categories whose total clears a threshold? (The filter is on the total, so it belongs in `HAVING`, not `WHERE`.)