The Budget Line

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

L5

Asked in

W. W. Grainger

Problem

Our cloud bill lands as individual cost lines in cloud_costs and budgets as allocation lines per service and region in cost_allocs, so a cost line is judged only against the allocation lines for its own service and region, with every such pairing reported as its own row. Flag each pairing 'over' or 'under' by comparing the cost line's amount to the allocation's amount, and 'on_target' for anything else, including a cost line with no recorded amount. Show the service, region, actual cost, budget and the flag.

Some rows are over. Some are under. Label every one.

Tables

cloud_costs

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

cost_allocs

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is a composite-key join wearing a FinOps costume, and a many-to-many one at that. Anyone can write the CASE; what separates candidates is attaching each cost line to the budgets for its own svc_name AND region. Match on the service alone and a 'us-west-2' cost is judged against every region's S3 budget, so FinOps chases spend that was inside its allocation. The second tell is the missing value: a cost line with a NULL amount must still come out 'on_target', and only an ELSE delivers that. Reach for a LEFT JOIN to be safe and every uncovered service slips through that same ELSE looking compliant.

Break down the requirements

Step 1: Confirm the join grain

A cost line and a budget line belong together only when both svc_name and region match. Neither table has one row per service and region: each holds several lines per key, and the question pairs every cost line with every allocation line for that key, one output row per pairing. That is why the preview shows the same S3 cost of 19.33 twice, once against 83.21 and once against 150.0. Joining on svc_name alone lets a 'us-west-2' cost collide with a 'eu-west-1' budget.

Step 2: Let the pairing decide the join type

The result reports pairings, and a cost line whose service and region have no allocation line has nothing to pair with. Cloud Run, Blob Storage, Cosmos DB and Pub/Sub never appear in cost_allocs at all. A plain JOIN drops those lines, which is the answer. A LEFT JOIN would keep them with a NULL budget and add rows the result should not have.

Step 3: Write the flag with an ELSE

Write WHEN cc.amount > ca.amount THEN 'over', then WHEN cc.amount < ca.amount THEN 'under', then ELSE 'on_target'. The ELSE is load-bearing. Some EC2 cost lines in 'us-east-1' carry no recorded amount, so both comparisons come back NULL, and the statement rules that anything neither over nor under is 'on_target'. Writing a third WHEN cc.amount = ca.amount instead leaves those rows with a NULL flag.

Step 4: Project the audit columns

Return svc_name and region from the cost side, cc.amount AS actual_cost, ca.amount AS budget and the flag. Both tables have a column called amount, so the aliases are not cosmetic: a bare amount is ambiguous, and the result needs both numbers beside the label.

The solution

SELECT
  cc.svc_name,
  cc.region,
  cc.amount AS actual_cost,
  ca.amount AS budget,
  CASE
    WHEN cc.amount > ca.amount THEN 'over'
    WHEN cc.amount < ca.amount THEN 'under'
    ELSE 'on_target'
  END AS budget_flag
FROM cloud_costs cc
JOIN cost_allocs ca
  ON cc.svc_name = ca.svc_name
 AND cc.region = ca.region

Common follow-up questions

Common follow-up questions

• How would you judge each cost line only against the allocation for its own billing month? (Tests matching `bill_date` to `period` as a third join key, and what happens to cost lines whose month has no allocation.)
• How would you express percent over budget instead of a flag? (Pushes a division guarded with `NULLIF(ca.amount, 0)`, plus what a negative budget (a credit) means.)
• Switch to `LEFT JOIN` and surface services with no budget row. How does the `CASE` change? (Probes `NULL`-aware classification and three-valued logic.)
• Roll up to one row per service and region with a single flag. How? (Forces a `SUM` on each side before joining, so the many-to-many fan-out does not multiply the totals.)