Allocations in Top Spending Region

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

L3

Problem

Each row of cloud_costs is one billed line item with its region and amount, and each row of cost_allocs is a team's allocation of spend to a region. Find the region whose billed line items add up to the most, then list every allocation record for that region in full, lowest allocation id first.

The biggest spenders live in one region.

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

This is an argmax over a per-region sum, dressed up as a FinOps allocation pull. Anyone can write the final filter on cost_allocs; what separates candidates is where the region comes from. 'Highest total spend' means summing every amount in cloud_costs per region and keeping the top total, not finding the single largest bill. On this data the biggest line item, 1784.5, sits in 'us-central1', while 'us-west-2' adds up to more. Grab the max row and you return every allocation for the wrong region, and nothing in the output looks broken.

Getting to the answer

Step 1: Total amount per region in cloud_costs

The ranking lives in the cost table, so GROUP BY region with SUM(amount) there. SUM skips the NULL amounts and nets the negative credits in, which is exactly what a bill total means. Leave cost_allocs alone at this point: it has its own region and amount, and its totals answer a different question.

Step 2: Keep only the top region

ORDER BY SUM(amount) DESC, region ASC with LIMIT 1 reduces the totals to one region, and the top_region CTE gives that value a name. The region ASC key never fires on this seed because no two totals match, but it is what keeps the pick deterministic the day two regions do.

Step 3: Filter cost_allocs to that region

Compare each allocation's region to (SELECT region FROM top_region), a scalar subquery that yields the one winning region. Return every allocation column and sort by alloc_id, lowest first.

WITH top_region AS (
  SELECT region
  FROM cloud_costs
  GROUP BY region
  ORDER BY SUM(amount) DESC, region ASC
  LIMIT 1
)
SELECT
  ca.alloc_id,
  ca.team_name,
  ca.svc_name,
  ca.region,
  ca.amount,
  ca.period,
  ca.acct_id,
  ca.category
FROM cost_allocs ca
WHERE ca.region = (SELECT region FROM top_region)
ORDER BY ca.alloc_id;

Biggest line item

ORDER BY amount DESC LIMIT 1 on cloud_costs returns the region of one bill: 'us-central1'. Every allocation that follows belongs to the wrong region.

Biggest total

GROUP BY region, then ORDER BY SUM(amount) DESC LIMIT 1, returns the region whose bills add up to the most: 'us-west-2', with its 26 allocation records.

What the interviewer is really checking

Common follow-up questions

• How would you return the allocations for the top three regions by total spend instead of one? (Tests moving from a single value to a small set: the filter becomes membership in that set.)
• Credits arrive as negative `amount` rows. If finance wants gross spend before credits, what changes? (Tests reshaping the metric with a conditional sum without touching the selection logic.)
• `provider` is spelled 'aws' and 'AWS' in `cloud_costs`. If the question became the top provider instead of the top region, what would you do first? (Tests normalizing a dirty key before it fragments the totals.)
• What if `cloud_costs` and `cost_allocs` named regions differently, say 'us-west-2' in one and 'US West 2' in the other? (Tests mapping keys across sources before comparing them.)