# Where the Money Goes

> Every provider has one service quietly eating the budget. Find it.

Canonical URL: <https://datadriven.io/problems/where-the-money-goes>

Domain: SQL · Difficulty: hard · Seniority: mid

## Problem

Finance is auditing cloud spend, and the provider names arrive inconsistently cased across the billing exports, so the same vendor shows up under several spellings. For each provider, find the single service with the highest total cost, and show those winners with the biggest spend first.

## Worked solution and explanation

### What this problem is really testing

This is a top-one-per-group query wearing a finance costume, and the whole thing hinges on a detail that never gets said out loud: 'aws' and 'AWS' are the same vendor. Anyone can sum amount by service. What separates candidates is whether they unify the provider casing BEFORE they group, and whether they reach for a partitioned row number instead of trying to bolt the per-provider maximum onto a plain GROUP BY. Skip the normalization and you split one provider into two ledgers, each with its own fake 'top service'. Skip the partitioning and you cannot get the winning service name back out of an aggregate.

---

### Break down the requirements

#### Step 1: Unify the vendor before anything else

provider holds 'aws', 'AWS', 'gcp', 'GCP', 'Azure'. Group on LOWER(provider) so every spelling of a vendor collapses into one bucket. Do this in the first pass, not after, or the sums are already wrong by the time you rank.

#### Step 2: Total spend at provider-plus-service grain

SUM(amount) grouped by LOWER(provider) and svc_name gives you one row per vendor-service pair. This is the grain the question actually cares about; the raw billing lines are noise until they are rolled up here.

#### Step 3: Keep only each provider's leader

ROW_NUMBER() OVER (PARTITION BY provider ORDER BY total_spend DESC, svc_name) numbers services within each provider by spend. Filter to rn = 1. The svc_name tiebreaker makes the winner deterministic when two services spend the same, so the graded result is stable.

#### Step 4: Order the winners

ORDER BY total_spend DESC puts the heaviest line item at the top, so the biggest single cost driver across all providers reads first.

---

### The solution

**Top service per provider**

```sql
WITH provider_service AS (
  SELECT LOWER(provider) AS provider,
         svc_name,
         SUM(amount) AS total_spend
  FROM cloud_costs
  GROUP BY LOWER(provider), svc_name
),
ranked AS (
  SELECT provider,
         svc_name,
         total_spend,
         ROW_NUMBER() OVER (PARTITION BY provider ORDER BY total_spend DESC, svc_name) AS rn
  FROM provider_service
)
SELECT provider,
       svc_name AS top_service,
       total_spend
FROM ranked
WHERE rn = 1
ORDER BY total_spend DESC
```

> **The two-pass shape**
>
> Aggregate first, rank second. You cannot do both in one SELECT because ROW_NUMBER needs the already-summed total_spend to order by. The CTE that rolls up to vendor-service grain, then a second CTE that numbers within each vendor, is the reusable pattern for every 'top N per group' question.

> **Cost Analysis**
>
> At real scale cloud_costs is a fact table of billing line items, easily 200M rows partitioned by bill_date. The rollup is a single hash aggregation on (LOWER(provider), svc_name), which collapses to a few dozen groups, so the window pass runs over a tiny intermediate. LOWER(provider) is computed at aggregation time and costs nothing extra; the expensive part is the one scan, and it stays a single pass with no self-join.

> **Interviewers Watch For**
>
> Whether you normalize provider casing without being told, and whether you pick a deterministic tiebreak. A candidate who says out loud 'if two services tie on spend I break it by name so the answer is stable' is signalling they think about grader-proof, reproducible output, not just a query that happened to work on the sample.

> **Common Pitfall**
>
> GROUP BY provider (raw) then MAX(total_spend) returns the right number but the WRONG service name: once you aggregate, svc_name is no longer tied to the max unless you correlate it back. The window function keeps the winning row intact. And grouping on raw provider silently reports 'aws' and 'AWS' as two vendors.

---

## Common follow-up questions

- Return the top TWO services per provider instead of just one. _(Tests whether they understand ROW_NUMBER generalizes: change the filter to rn <= 2. Also opens the RANK vs ROW_NUMBER tie-handling discussion.)_
- Scope this to a single billing year and explain how partition pruning helps. _(Pushes a bill_date predicate onto the base scan of a date-partitioned fact table, and checks they know the filter must land before the aggregation.)_
- What if region should also be normalized and the answer is top service per provider per region? _(Extends the partition key and re-raises the casing-hygiene question for a second dimension.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/where-the-money-goes)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.