# Where the Money Sits > Every team carries a cost, and some of it is what keeps the servers running. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## 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. ## 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 **Mixed conditional and unconditional aggregation** ```sql 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 ``` > **One pass, both numbers** > > One scan of 12M rows. The filtered count and the full sum ride the same aggregation, so there is no second pass and no self-join. Output is one narrow row per team, cheap to sort or ship to a dashboard. > **Interviewers watch for** > > Whether you compute both metrics in a single pass instead of running two queries and stitching them. Keeping the 'compute' condition inside the aggregate, not in the WHERE clause, is the exact move that signals aggregate fluency. > **Common pitfall** > > Moving 'compute' into a WHERE clause to make the count read cleaner. It quietly excludes every non-compute allocation from total_cost, undercounting spend with no error to flag it. The condition belongs inside the CASE, so only the count is scoped. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_money_sits) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.