# Top of the Bill

> Every cloud bill has a headliner or two. Find them.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Finance is reviewing the cloud bills and wants to see which services dominate the spend. Among the services that also appear in the cost allocations, find the two with the highest total cloud cost.

## Worked solution and explanation

### What this really is

Underneath the FinOps framing this is a semi-join feeding a top-N. The real question the interviewer is probing: can you express 'services that also have allocations on file' as a membership test, not a row-multiplying join? Reach for a plain INNER JOIN on svc_name and every service with many allocation rows gets its cloud cost summed once per allocation, so the totals balloon and the top two you report is not the top two finance sees. cost_allocs is a filter here, never a source of dollars: the moment you SUM across the join, or add its amounts in, the numbers stop tying out.

---

### Reading the requirements

#### Step 1: Restrict to allocated services without fanning out

The requirement is membership, not a join: cc.svc_name IN (SELECT svc_name FROM cost_allocs). This keeps each cloud_costs row exactly once. An INNER JOIN would duplicate a cost row once per matching allocation and inflate SUM(cc.amount).

#### Step 2: Aggregate and take the two heaviest

SUM(cc.amount) GROUP BY cc.svc_name gives per-service spend, then ORDER BY total_amount DESC LIMIT 2 takes the two heaviest. Sum cc.amount only; cost_allocs is a filter, never a metric source.

---

### The solution

**TOP TWO SERVICES BY COST**

```sql
SELECT cc.svc_name,
       SUM(cc.amount) AS total_amount
FROM cloud_costs cc
WHERE cc.svc_name IN (SELECT svc_name FROM cost_allocs)
GROUP BY cc.svc_name
ORDER BY total_amount DESC
LIMIT 2;
```

> **The fan-out that looks correct**
>
> Writing INNER JOIN cost_allocs ON cc.svc_name = ca.svc_name feels natural, and it runs clean. But if cost_allocs holds several rows per service (different teams, periods, categories), each cloud cost row is counted once per allocation and SUM(cc.amount) multiplies. The query looks right until your numbers do not tie out to finance's.

**Wrong (join fans out)**

INNER JOIN cost_allocs ca ON cc.svc_name = ca.svc_name duplicates each cost row once per matching allocation, so SUM(cc.amount) is multiplied by the allocation count per service.

**Right (semi-join)**

cc.svc_name IN (SELECT svc_name FROM cost_allocs) is a membership test: each cost row survives exactly once and the sum stays honest.

> **Why a semi-join, not a join**
>
> cloud_costs is 15M rows. Writing the filter as cc.svc_name IN (SELECT svc_name FROM cost_allocs) lets the planner build the allocation service set once (a hash semi-join) and probe it per cost row, so cost_allocs is touched a single time. An EXISTS correlated subquery is logically identical and usually plans the same. What you never want is a JOIN you then have to de-duplicate: that both fans out the sum and does more work.

> **Interviewers watch for**
>
> Two tells separate seniors. First, do you catch that a plain join to cost_allocs multiplies the sum when a service has several allocation rows? Naming that cardinality risk unprompted is the signal. Second, do you ask what happens if two services tie for second? LIMIT 2 picks one arbitrarily; if both should surface, DENSE_RANK() OVER (ORDER BY SUM(cc.amount) DESC) <= 2 is the move.

---

### Common follow-up questions

## Common follow-up questions

- Rewrite it so that a tie for second place surfaces every tied service. _(Forces the DENSE_RANK refactor and tests whether you understand LIMIT's tie behavior.)_
- How would you confirm cost_allocs does not inflate the per-service totals? _(Probes join-cardinality discipline: a COUNT(*) versus COUNT(DISTINCT svc_name) sanity check on cost_allocs.)_
- What if finance only wants a single billing year's spend? How would you add that window? _(Adds a date predicate on bill_date and tests keeping it sargable so partition pruning fires on the 15M-row table.)_
- How would you show each service's allocated budget alongside its actual cloud cost? _(Tests joining back to cost_allocs after aggregation without reintroducing fan-out.)_

## Related

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