Total Engineering Cost Allocation

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

Forbes

Problem

The CFO is reviewing the cloud allocation total for engineering. Engineering teams in the allocation table are 'data-eng', 'backend', 'devops', and 'platform' (case-insensitive). What is the sum of allocated amounts across those teams?

Engineering's total allocated budget.

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 asking

This is a case-folded membership filter dressed up as a CFO roll-up. Anyone can write SUM(amount) with an IN list. What separates candidates is noticing that team_name stores the same team under more than one spelling: 'platform' next to 'Platform', 'data-eng' next to 'DATA-ENG'. Match the four names byte for byte and those rows quietly fall out. The total still looks like a perfectly plausible number, and the CFO walks into the review with an engineering figure that is too low.

Break down the requirements

Step 1: Fold the case of team_name

Wrap the column in LOWER() before the comparison so 'Platform' and 'platform' become one value. Fold the column, not the literals: the literals are already lowercase, so folding only them changes nothing.

Step 2: Restrict to the four engineering teams

LOWER(team_name) IN ('data-eng', 'backend', 'devops', 'platform'). 'ml-team', 'frontend', 'security', 'mobile' and the small business teams live in the same table and must stay out.

Step 3: Sum the amount into one number

The CFO wants one figure, so there is no GROUP BY. Keep the negative rows: the -120.5 on 'backend' is a credit, and it nets against the spend exactly as finance books it.

The solution

SELECT SUM(amount) AS total_amount
FROM cost_allocs
WHERE LOWER(team_name) IN ('data-eng', 'backend', 'devops', 'platform');

Exact match

WHERE team_name IN ('data-eng', 'backend', 'devops', 'platform') keeps only the rows already spelled in lowercase. Every 'Platform' and 'DATA-ENG' row is dropped, and the total comes out short with no error to warn you.

Case-folded match

WHERE LOWER(team_name) IN (...) compares the folded spelling, so every casing of the four teams lands in the sum and the answer is 1037131.75.

Common follow-up questions

Common follow-up questions

• How would you produce a per-team breakdown instead of one total? (Group by `LOWER(team_name)` and select the normalized name, so 'Platform' and 'platform' stay one row.)
• What if the CFO asks for only the current quarter? (Add a `period` predicate; it is the partition key, so the planner prunes partitions.)
• How do you guard against future team-name typos? (Promote the four teams into a dimension table with a stable `team_id` and filter on the surrogate key.)