# Shadow Spend

> Region by region. Service by service. Where does the money go?

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The FinOps team wants a single view of cloud spend that merges the billed cost records with the internal allocation records, counting a line item that appears in both only once. Drop any row with no amount or no region, then report the total spend for each region and service pairing, highest first.

## Worked solution and explanation

### What this really is

Strip the FinOps costume and this is a set union with a dedup decision hiding inside one word. Two tables describe the same spend from different angles, and the same line item can land in both. The whole problem turns on one choice: the operator that collapses identical rows versus the one that keeps every copy. Keep the copies and you double-count every shared line item; collapse them but forget to strip the NULL amounts and regions first and two half-blank rows survive as distinct. Everyone can stack two SELECTs. The tell is whether you hear which set operator the word 'duplicate' is quietly demanding.

---

### Break down the requirements

#### Step 1: Union the two tables

Select `region`, `svc_name`, `amount` from both `cloud_costs` and `cost_allocs`, using UNION so identical line items collapse to a single row.

#### Step 2: Filter out NULLs

Exclude rows where `amount IS NULL OR region IS NULL`, and do it inside each branch so the dedup never sees a half-blank row.

#### Step 3: Aggregate and sort

`GROUP BY region, svc_name` with `SUM(amount)`, ordered by the total descending.

---

### The solution

**Deduplicated union with null filtering**

```sql
WITH combined AS (
    SELECT region, svc_name, amount FROM cloud_costs
    WHERE amount IS NOT NULL AND region IS NOT NULL
    UNION
    SELECT region, svc_name, amount FROM cost_allocs
    WHERE amount IS NOT NULL AND region IS NOT NULL
)
SELECT region, svc_name, SUM(amount) AS total_spend
FROM combined
GROUP BY region, svc_name
ORDER BY total_spend DESC
```

> **Cost Analysis**
>
> UNION (not UNION ALL) deduplicates 12M + 18M = 30M rows, which forces a sort or hash to detect identical rows. The GROUP BY then collapses to (regions x services), a tiny result. The deduplication pass is the most expensive operation in the plan.

> **Interviewers Watch For**
>
> Whether you reach for UNION or UNION ALL. Because the ask is to count shared line items once, UNION is correct here. This is the exact case that inverts the usual reflex to default to UNION ALL for speed.

> **Common Pitfall**
>
> Filtering NULLs after the UNION instead of before it. Two rows that differ only in a NULL amount can slip through as distinct, so the dedup keeps both. Filter inside each branch and the set operator compares clean rows.

---

## Common follow-up questions

- What defines a 'duplicate line item' across the two tables? _(Tests whether deduplication should be on all columns or a subset. UNION deduplicates on every selected column.)_
- How would performance change with UNION ALL and a separate DISTINCT step? _(Tests understanding of query plan differences: UNION sorts during the merge; a separate DISTINCT sorts after.)_
- What if the two tables recorded amounts at different scales, such as dollars versus cents? _(Tests data normalization awareness before combining sources.)_

## Related

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