# Three Clouds

> AWS, GCP, Azure. Side by side.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The FinOps team is reconciling invoices from the three major cloud providers. Pull every cost amount associated with AWS, GCP, or Azure.

## Worked solution and explanation

### The trap hiding in plain sight

This looks like a one-line SELECT, and it is. The entire test is whether you notice that `provider` is stored in inconsistent casing before you write the filter. Glance at the data and you see 'AWS', 'aws', 'GCP', 'gcp', and 'Azure' all mixed together. Write the obvious `WHERE provider IN ('aws','gcp','azure')` and the string comparison is case-sensitive: you match only the lowercase rows, drop every 'AWS' and 'GCP', and lose every single 'Azure' row. FinOps gets a reconciliation missing most of its invoices. The fix is trivial once you see it. Seeing it is the whole point.

> **Trick to solving**
>
> Normalize the case, do not enumerate it. Wrapping the column in `LOWER(provider)` collapses every casing variant to one form, so a single three-value list matches them all. Trying to spell out the variants instead ('AWS','aws','GCP','gcp','Azure','azure') is brittle and breaks the moment a new casing like 'Aws' appears.

---

### How to get there

#### Step 1: Read the data before the filter

Before writing any SQL, scan the `provider` values. They arrive from three separate billing exports with no shared casing convention, so the same provider shows up as both 'AWS' and 'aws'. That single observation is what the problem is really testing.

#### Step 2: Normalize, then match

Apply `LOWER(provider)` so every variant folds to a single lowercase form, then compare against the lowercase literals 'aws', 'gcp', and 'azure'. Now all three providers match regardless of how each export capitalized them.

#### Step 3: Return amounts in reconciliation order

The FinOps team reads the reconciliation from smallest charge to largest, so return the amounts in ascending order. That ordering is visible in the expected output preview.

---

### The solution

**Case-insensitive provider filter**

```sql
SELECT amount
FROM cloud_costs
WHERE LOWER(provider) IN ('aws', 'gcp', 'azure')
ORDER BY amount;
```

**Case-sensitive (wrong)**

WHERE provider IN ('aws','gcp','azure') matches only the lowercase 'aws' and 'gcp' rows. Every 'AWS', 'GCP', and 'Azure' row is dropped, so you return a fraction of the real cost data and never notice the gap.

**Case-normalized (correct)**

WHERE LOWER(provider) IN ('aws','gcp','azure') folds all casings to one form before comparing, so all three providers are captured no matter how each billing export wrote them.

> **Common pitfall**
>
> The most common failure here is not a syntax error. It is a query that runs cleanly, returns rows, and is quietly wrong because the case-sensitive IN silently excludes the capitalized variants. Nothing errors, so the mistake survives until someone reconciles the totals by hand.

> **Interviewers watch for**
>
> Interviewers watch whether you inspect the actual column values before trusting the prompt's wording. A candidate who writes IN ('aws','gcp','azure') without checking the casing reveals they filter on assumptions; one who reaches for LOWER() shows they validate the data first.

> **Performance insight**
>
> Wrapping `provider` in `LOWER()` makes the predicate non-sargable, so a plain index on `provider` cannot be used directly and the engine scans. On this 8M-row table that is acceptable. If it became hot, a functional index on `LOWER(provider)` (or storing a normalized casing at ingest) restores index seeks.

---

## Common follow-up questions

- If additional casing variants like 'Aws' or 'aWs' appeared in a future export, would your query still match them, and why? _(Tests whether the candidate's normalization generalizes beyond the two casings in the sample.)_
- How would you surface provider values that are neither AWS, GCP, nor Azure so the FinOps team can catch typos or unknown vendors? _(Tests data-quality awareness and scoping of the IN list.)_
- If this filter had to run thousands of times per minute, how would you keep the case-insensitive match index-friendly? _(Tests understanding of why LOWER() defeats a plain index and how to fix it.)_

## Related

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