Service Budget per Head

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

Microsoft

Problem

Team names in cost_allocs were typed by hand, so a name that differs only in letter case is still the same team. A service's budget exposure grows with its billing volume: every allocation dollar counts once for each line that service has in cloud_costs, and a service with no billing lines is left off the report entirely. An analyst wants each service's budget per head, its exposure split evenly across its unique teams, rounded to a whole number.

Budget per head. Pipeline by pipeline.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

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 really is

This is a fan-out you keep on purpose, with a divisor that has to survive it. Joining cloud_costs to cost_allocs on svc_name copies every allocation row once per billing line, and the statement wants exactly that in the numerator. Anyone can write the join. What separates candidates is seeing that the same copies land in the divisor too, and that only a unique count of teams is immune to them. The second trap is quieter: team_name was typed by hand, so one team appears in two letter cases. Count the raw spellings and every affected service splits its exposure across a phantom team, landing well under the true figure.

Build it in three moves

Step 1: Match on svc_name and nothing else

An INNER JOIN on svc_name keeps only services that have both billing lines and allocated teams, which is the report's row set. Adding region or acct_id to the condition looks careful and silently shrinks every total.

Step 2: Let SUM(ca.amount) fan out

After the join each allocation dollar appears once per billing line of its service, so the plain sum already is the exposure. Deduping cloud_costs before the join removes the very multiplier the statement asks for.

Step 3: Count each team once, whatever its casing

COUNT(DISTINCT LOWER(ca.team_name)) collapses the join copies and the case variants into one entry per real team. Multiplying by 1.0 before dividing keeps an integer amount from truncating, and ROUND takes it to a whole number.

SELECT cc.svc_name,
       ROUND(SUM(ca.amount) * 1.0 / COUNT(DISTINCT LOWER(ca.team_name))) AS budget_per_head
FROM cloud_costs cc
INNER JOIN cost_allocs ca ON cc.svc_name = ca.svc_name
GROUP BY cc.svc_name

SELECT cc.svc_name,
       ROUND(SUM(ca.amount) * 1.0 / COUNT(ca.team_name)) AS budget_per_head
FROM cloud_costs cc
INNER JOIN cost_allocs ca ON cc.svc_name = ca.svc_name
GROUP BY cc.svc_name

Spelling as written

COUNT(DISTINCT ca.team_name) treats 'Data Platform' and 'data platform' as two teams, so the divisor is too big wherever the casing drifted.

One team per name

COUNT(DISTINCT LOWER(ca.team_name)) folds the variants together, and every service splits its exposure across the teams that actually exist.

Common follow-up questions

• If `cloud_costs` held accidental duplicate billing lines, what happens to `budget_per_head`, and how would you guard against it? (The numerator scales with billing lines by design, so duplicate lines inflate exposure silently.)
• Rewrite this so the join touches 300 rows per side instead of billions. (Pre-aggregate both sides by `svc_name`, then multiply the allocation total by the billing-line count.)
• What if `team_name` also had stray whitespace or abbreviations, and where should that cleanup live? (Folding case fixes one kind of drift; trailing spaces or abbreviations need a cleaned team dimension.)