# Beyond the Pacific

> Some services never crossed into Asia-Pacific. Those are the ones to price.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We're auditing our AWS and GCP cloud spend and want the priciest single charge for each service that stays out of Asia-Pacific regions entirely. Limit it to services whose charges average at least 20, and return each service with its top charge.

## Worked solution and explanation

### What this really tests

This is a per-service set exclusion wearing a multi-cloud FinOps costume. The skill being probed: can you express 'services that NEVER show up in Asia-Pacific' as anti-membership scoped to the WHOLE service, across two clouds whose `provider` column is spelled every which way? Two traps do the sorting. First, a case-sensitive `provider = 'aws'` drops every 'AWS' row and half your data goes missing. Second, filtering Asia-Pacific rows out row-by-row instead of excluding the whole service leaves you pricing a partial slice of a service that DOES touch Asia-Pacific. Get either wrong and the numbers look plausible while being about the wrong set entirely.

---

### Break it into three moves

#### Step 1: Pin the providers, case-insensitively

Match providers with `LOWER(provider) IN ('aws', 'gcp')` so mixed casing cannot drop rows, and use the SAME predicate inside the exclusion subquery so both sides agree on what an AWS or GCP row is.

#### Step 2: Exclude any service that ever touched Asia-Pacific

Asia-Pacific is a region PREFIX, and the two clouds spell it differently: AWS uses 'ap-', GCP uses 'asia-'. Anchor both at the start with `(region LIKE 'ap-%' OR region LIKE 'asia-%')`. The parentheses are load-bearing: `AND` binds tighter than `OR`, so without them the provider filter fuses to the first pattern only and unrelated 'asia-' rows leak in. Collect the offending services as `DISTINCT svc_name`, then drop them wholesale with `NOT IN`, not per row.

#### Step 3: Aggregate and gate on the average

Group by `svc_name`, take `MAX(amount)` as the top charge, and keep only services whose `AVG(amount) >= 20`.

---

### The solution

**TOP NON-APAC SERVICE COSTS ACROSS AWS AND GCP**

```sql
SELECT svc_name, MAX(amount) AS max_amount
FROM cloud_costs
WHERE LOWER(provider) IN ('aws', 'gcp')
  AND svc_name NOT IN (
    SELECT DISTINCT svc_name
    FROM cloud_costs
    WHERE LOWER(provider) IN ('aws', 'gcp')
      AND (region LIKE 'ap-%' OR region LIKE 'asia-%')
  )
GROUP BY svc_name
HAVING AVG(amount) >= 20
```

> **Cost Analysis**
>
> `LOWER(provider)` defeats a plain btree index on `provider`; on 15M rows a functional index on `LOWER(provider)` earns its keep. Planners typically rewrite the `NOT IN` subquery as a hash anti-join, so the exclusion set is built once, not re-scanned per outer row.

> **Interviewers Watch For**
>
> Whether you flag the `NOT IN` plus NULL hazard unprompted. Here `svc_name` is NOT NULL so it is safe, but the instant the exclusion key can be NULL, one NULL row makes the outer predicate UNKNOWN for every row and the result silently collapses to empty. `NOT EXISTS` is null-safe and the answer interviewers want to hear.

> **Common Pitfall**
>
> Putting `region NOT LIKE 'ap-%'` in the OUTER WHERE. That drops the Asia-Pacific rows but KEEPS the service as long as it has any non-Asia-Pacific charge, so `MAX` and `AVG` run over a partial slice of a service that genuinely operates in Asia-Pacific. The exclusion has to remove the whole service, which is why it lives in a subquery.

---

### Common follow-up questions

## Common follow-up questions

- Rewrite the exclusion with NOT EXISTS and explain when it actually matters. _(Probes naming the NOT IN plus NULL issue and the null-safe rewrite.)_
- What changes if we only care about services with no Asia-Pacific billing in the last 30 days? _(Tests scoping a date filter inside the exclusion without leaking it to the outer aggregate.)_
- A new cloud names its Tokyo region 'jp-east-1'. How do you keep the Asia-Pacific match honest as providers multiply? _(Forces a conversation about how many region-naming schemes a prefix match has to anticipate.)_

## Related

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