Where the Money Sits

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

LinkedIn

Problem

The finance team is building a per-team cost summary. For each team, report how many of its allocations fall in the 'compute' category, alongside the total cost across every allocation the team owns, whatever the category.

Every team carries a cost, and some of it is what keeps the servers running.

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

Beneath the finance-dashboard framing, this is two aggregates riding one scan over the same rows: a filtered count of compute allocations sitting next to an unfiltered sum of every dollar. Anyone can sum a column. The move that separates candidates is keeping the 'compute' condition inside the aggregate instead of the WHERE clause. Push it into a WHERE and total_cost silently drops every non-compute row, so finance sees a spend number that is too small with no error to explain it.

Break down the requirements

Step 1: Group by team

GROUP BY team_name produces one row per team, so both metrics land on the same line of the report.

Step 2: Count compute allocations

SUM(CASE WHEN category = 'compute' THEN 1 ELSE 0 END) counts only the compute allocations while still visiting every row. Teams with no compute rows get 0 instead of vanishing.

Step 3: Sum total cost

SUM(amount) runs over the exact same grouped rows, unfiltered, so the total reflects the whole team's spend across every category.

The solution

SELECT
    team_name,
    SUM(CASE WHEN category = 'compute' THEN 1 ELSE 0 END) AS compute_count,
    SUM(amount) AS total_cost
FROM cost_allocs
GROUP BY team_name

Common follow-up questions

• How would you also show the percentage of a team's allocations that are compute? (Divide `compute_count` by COUNT(*) and multiply by 100, still in a single pass.)
• What if you also needed a count restricted to a recent period window? (Tests moving the condition from a category match to a date-range predicate inside the same CASE.)
• How would you turn this into counts per category across the columns of one row? (Tests one conditional SUM per category to pivot counts into columns.)