# The Quiet Drain

> Some AWS services quietly drain the budget.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The FinOps team flags any AWS line item costing 200 or more as high-cost. Broken down by region, count how many different AWS services carry at least one high-cost entry, from the most down to the fewest.

## Worked solution and explanation

### What this is really asking

Underneath the FinOps framing this is a per-region distinct count with a catch: the `provider` column is dirty. The same vendor shows up as both 'aws' and 'AWS', so an exact `provider = 'aws'` match silently throws away half the rows and every region count comes back too low. The two moves that separate a clean answer: fold the casing before you filter, and count distinct `svc_name` so a service billed a hundred times still counts once.

---

### The two traps hiding in the data

#### Step 1: Normalize the provider casing

The sample data carries 'aws' and 'AWS' for the same vendor. `WHERE provider = 'aws'` matches only the lowercase spelling and drops the rest, so `LOWER(provider) = 'aws'` folds both into one filter before anything else runs.

#### Step 2: Threshold, then distinct services per region

Keep entries where `amount >= 200` (the prompt names 200 the high-cost floor, so the boundary is inclusive), group by `region`, and use `COUNT(DISTINCT svc_name)` so repeat line items for the same service collapse to a single count.

---

### The solution

**Distinct AWS services per region**

```sql
SELECT region,
       COUNT(DISTINCT svc_name) AS service_count
FROM cloud_costs
WHERE LOWER(provider) = 'aws'
  AND amount >= 200
GROUP BY region
ORDER BY service_count DESC, region
```

> **Common Pitfall**
>
> Writing `provider = 'aws'` instead of `LOWER(provider) = 'aws'`. It runs without error and returns plausible-looking numbers, which is exactly what makes it dangerous: every region that logged an 'AWS'-cased row is undercounted, and nobody notices until the totals get challenged.

> **Interviewers Watch For**
>
> Whether you ask if `provider` casing is consistent, whether 200 is inclusive, and whether a high-cost entry means one raw line item or an aggregated monthly bill. Each assumption moves the count.

> **Cost Analysis**
>
> 8M rows partitioned by `bill_date` with no date predicate means a full scan across all 36 partitions. Wrapping `provider` in `LOWER()` also defeats a plain index on that column; a functional index on `LOWER(provider)`, or normalizing casing at ingest, keeps the filter sargable.

**Line-item volume**

SELECT region, COUNT(*) FROM cloud_costs WHERE provider = 'aws' AND amount >= 200 GROUP BY region. Counts every qualifying row and misses the 'AWS'-cased ones, so it answers a different question than the one asked.

**Distinct services**

COUNT(DISTINCT svc_name) with LOWER(provider) = 'aws' counts services rather than rows and captures both spellings, which is the number FinOps actually wants.

---

### Common follow-up questions

## Common follow-up questions

- How would you also show the total spend per region alongside the service count? _(Add SUM(amount) to the same SELECT; the WHERE and GROUP BY stay put.)_
- What changes if the 200 threshold should apply to each service's monthly total rather than a single line item? _(Pre-aggregate by (region, svc_name, month) in a CTE, filter the aggregate, then count distinct services.)_
- How would you scope this to a single billing month so partition pruning kicks in? _(Add a bill_date range predicate matching the partition key so only that month's partitions are scanned.)_

## Related

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