# Cloud Bill

> Which cost buckets are bleeding money?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The FinOps team is reviewing cloud spend for the quarterly budget cycle. For each spending category, they need the total dollar amount, the number of line items, and the average cost per line item. Skip any allocations with a missing account ID. Show categories from the most expensive to the least.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests GROUP BY with NULL filtering and multiple aggregates. The requirement to skip NULL account IDs probes whether you apply the filter in WHERE (correct) or forget it entirely.

---

### Break down the requirements

#### Step 1: Filter out NULL account IDs

`WHERE acct_id IS NOT NULL` removes allocations without an account, as specified.

#### Step 2: Aggregate per category

`SUM(amount)` for total, `COUNT(*)` for line items, `AVG(amount)` for average cost per line item, grouped by `category`.

#### Step 3: Sort descending

`ORDER BY SUM(amount) DESC` ranks most expensive categories first.

---

### The solution

**Null-filtered multi-metric aggregation**

```sql
SELECT
    category,
    SUM(amount) AS total_amount,
    COUNT(*) AS line_items,
    AVG(amount) AS avg_cost_per_item
FROM cost_allocs
WHERE acct_id IS NOT NULL
GROUP BY category
ORDER BY total_amount DESC
```

> **Cost Analysis**
>
> Single scan of 2M rows with NULL filter. The GROUP BY reduces to a handful of categories. Trivially fast at this scale.

> **Common Pitfall**
>
> Forgetting `WHERE acct_id IS NOT NULL` includes rows the prompt explicitly asks to skip. Always re-read filtering requirements before writing the GROUP BY.

---

## Common follow-up questions

- What if you needed to show how many rows were excluded by the NULL filter? _(Use a separate COUNT with CASE or a UNION with the filtered-out count.)_
- How would you add a percentage-of-total column? _(Tests window functions: SUM(amount) OVER () for the grand total denominator.)_
- What if acct_id is empty string instead of NULL? _(Tests awareness that '' IS NOT NULL is true; you would need WHERE acct_id IS NOT NULL AND acct_id != ''.)_

## Related

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