Cost Share Within Category

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Google

Problem

List every compute, storage and network allocation in cost_allocs with its allocation ID, category, amount, and the percent of its category's total spend it accounts for, counting a whole category as 100, sorted by category and then by allocation ID. The FinOps analyst reading it wants amounts in whole dollars with the cents dropped, though the percentages still come from the exact amounts.

Each entry's slice of the category total.

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

What this is really testing

This is a share-of-total dressed up as a FinOps review. The question is whether you can put each category's total beside every one of its allocations without collapsing them. Anyone can total spend per category. The trap is doing it with GROUP BY category: the totals come out right, but the 101 allocation rows fold into three, alloc_id can no longer be selected, and no row is left to hold a per-allocation percentage. A window SUM(amount) OVER (PARTITION BY category) attaches the total in one pass and keeps every row.

Break down the requirements

Step 1: Filter to the three categories

WHERE category IN ('compute', 'storage', 'network') drops 'database', 'ml' and 'other', including the -120.50 credit sitting in 'ml'. The window is evaluated after WHERE, so it only sees the rows you keep.

Step 2: Put the category total on every row

SUM(amount) OVER (PARTITION BY category) gives each row the total of its own category. There is no ORDER BY inside the window, so the frame is the whole partition. The rows stay and the denominator rides along with them.

Step 3: Turn the share into a percentage

100 * amount / SUM(amount) OVER (...) computes the percentage from the exact amounts. amount is REAL, so the division is floating point and nothing gets truncated to zero.

Step 4: Drop the cents only for display

CAST(amount AS INTEGER) turns 449.26 into 449 for whole_dollar_amount. It touches only the displayed column; the percentage keeps reading the exact amount. Then sort by category and alloc_id.

SELECT
    alloc_id,
    category,
    CAST(amount AS INTEGER) AS whole_dollar_amount,
    100 * amount / SUM(amount) OVER (PARTITION BY category) AS pct_of_category
FROM cost_allocs
WHERE category IN ('compute', 'storage', 'network')
ORDER BY category, alloc_id

Per-category total

GROUP BY category with SUM(amount) returns three rows. alloc_id and each allocation's amount are gone, so there is nothing left to divide.

Total beside each row

SUM(amount) OVER (PARTITION BY category) returns all 101 allocations, each carrying its category's total, so 100 * amount / total works on every row.

Common follow-up questions

• What if you also needed a cumulative percentage within each category? (Adding `ORDER BY alloc_id` inside the window turns the frame into a running total.)
• How would you keep only allocations above 10% of their category? (Filter on `pct_of_category` in an outer query or CTE, since a window alias cannot be used in `WHERE`.)
• What if a category's total could be zero? (Wrap the denominator in `NULLIF(..., 0)` so the share becomes `NULL` instead of an error.)