# Where the Money Pools

> Top spenders in one region.

Canonical URL: <https://datadriven.io/problems/where_the_money_pools>

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

For each service in the 'us-west-2' region, show the total cloud cost, sorted from highest spend to lowest.

## Worked solution and explanation

### The real problem beneath the billing story

Underneath the cloud-billing story this is a single-region rollup, and the whole thing turns on one decision: where the region filter lives. Put `region = 'us-west-2'` in `WHERE` and the engine cuts 12M rows down to one region before a single `SUM` runs. Reach for `HAVING` instead and it groups every region first, then discards most of that work; worse, `HAVING region = 'us-west-2'` filters on a non-aggregated column after the grouping and comes back either as an error or a silently wrong total. Anyone can group and sum. What separates candidates is knowing that `WHERE` runs before `GROUP BY` and `HAVING` runs after.

---

### Break down the requirements

#### Step 1: Filter to the target region

Apply `WHERE region = 'us-west-2'` to restrict the 12M row `cloud_costs` table to only the region of interest before any aggregation happens.

#### Step 2: Aggregate cost per service

`GROUP BY svc_name` with `SUM(amount)` produces one row per service with its total spend.

#### Step 3: Sort descending by total

`ORDER BY total_cost DESC` surfaces the highest spenders first, which is what the prompt requests.

---

### The solution

**Filter, aggregate, and sort by spend**

```sql
SELECT svc_name, SUM(amount) AS total_cost
FROM cloud_costs
WHERE region = 'us-west-2'
GROUP BY svc_name
ORDER BY total_cost DESC
```

> **Cost Analysis**
>
> The `WHERE` clause narrows 12M rows to roughly 1M (1 of 12 regions) before aggregation. An index on `(region, svc_name, amount)` would make this a covering index scan, avoiding a full table scan entirely.

> **Interviewers Watch For**
>
> Candidates who filter after grouping (using `HAVING region = ...`) instead of `WHERE` reveal a misunderstanding of SQL evaluation order. The `WHERE` clause is also more efficient because it reduces the input to the aggregation step.

> **Common Pitfall**
>
> Forgetting to alias `SUM(amount)` makes the output column name engine-dependent. Always alias aggregate expressions for deterministic column names in downstream consumption.

---

## Common follow-up questions

- How would you modify this to show the top 5 services only? _(Tests whether you use `LIMIT` vs. a window function depending on tie requirements.)_
- What if `amount` could be negative (representing credits)? _(Checks awareness that SUM handles negatives, and whether a net-cost filter is needed.)_
- How would you add a percentage-of-total column? _(Probes knowledge of window functions: `SUM(amount) / SUM(SUM(amount)) OVER ()`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/where_the_money_pools)
- [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.