Cloud Cost by Team

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

City and County of San Francisco

Problem

Credits land in cost_allocs as negative amounts, and they count against a team's spend. For the budget review, an analyst needs each team's total cost allocation, from highest to lowest.

Spend by team. Who's burning most?

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

This is one collapse of cost_allocs into a net total per team_name, dressed up as a budget review. Anyone writes SUM(amount). The grain is what separates candidates: the labels come in casing variants ('platform' and 'Platform', 'data-eng' and 'DATA-ENG'), and the expected output keeps each spelling as its own row. Reach for LOWER(team_name) to tidy them and you get 14 rows instead of 16, two totals inflated by the merge, a reshuffled top of the list, and a failed grade.

Getting to the query

Step 1: Settle the grain before writing anything

One output row per team_name value as stored. The preview settles it: 'platform' and 'Platform' each carry their own total, so the grouping key is the raw column with nothing wrapped around it.

Step 2: Sum every row, credits included

SUM(amount) nets the one negative row (a -120.5 credit on 'backend') against that team's spend. A WHERE amount > 0 guard looks careful and silently overstates backend.

Step 3: Sort on the alias, not the expression

ORDER BY total_cost DESC sorts on the aggregate you already named, so the sum is written once. The order is graded, so the sort is part of the answer, not presentation.

SELECT team_name, SUM(amount) AS total_cost
FROM cost_allocs
GROUP BY team_name
ORDER BY total_cost DESC

Tidied labels

GROUP BY LOWER(team_name) folds 'Platform' into 'platform' and 'DATA-ENG' into 'data-eng': 14 rows, two totals near 358k and 354k that exist nowhere in the expected output.

Labels as stored

GROUP BY team_name returns 16 rows, every spelling with its own total, exactly the shape the preview shows.

Common follow-up questions

• What if you needed only the top 5 teams? (Tests `LIMIT 5` and why it needs the `ORDER BY` to mean anything.)
• How would you show each team's percentage of total spend? (Tests a window over the aggregate: `100.0 * SUM(amount) / SUM(SUM(amount)) OVER ()`.)
• What if 'platform' and 'Platform' should be one team? (Tests grouping on `LOWER(team_name)`, and whether the label belongs fixed upstream instead.)