# Top AWS Non-APAC Service Costs

> Outside APAC, AWS costs tell a different story.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Among AWS cloud costs, find the highest cost for each service that never appears in Asia-Pacific regions, with a minimum average cost of 90 across all its entries. Show each service and its max amount.

## Worked solution and explanation

### Why this problem exists in real interviews

`NOT IN` against a subquery is where SQL silently lies: one `NULL` `svc_name` in the inner result and the outer query returns zero rows. This problem packages that landmine inside a routine FinOps ask about AWS spend outside Asia-Pacific. Get the exclusion right and the rest is a `GROUP BY` with `HAVING` on the per-service average.

---

### Break down the requirements

#### Step 1: Pin the provider

Filter `LOWER(provider) = 'aws'` so casing in `provider` cannot drop rows. Apply the same predicate inside the exclusion subquery so both sides agree.

#### Step 2: Exclude services that ever touched APAC

Blacklist with `region LIKE 'ap-%' OR region LIKE '%ap-%'`. We drop the whole service, not just APAC rows, so the subquery yields `DISTINCT svc_name` for `NOT IN`.

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

Group by `svc_name`, compute `MAX(amount)`, keep services where `AVG(amount) >= 90`.

---

### The solution

**TOP AWS NON-APAC SERVICE COSTS**

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

> **Cost Analysis**
>
> `LOWER(provider)` defeats a btree index on `provider`; on 15M rows, a functional index on `LOWER(provider)` pays off. Planners usually rewrite the `NOT IN` subquery as an anti-join.

> **Interviewers Watch For**
>
> Whether you flag the `NOT IN` plus `NULL` trap unprompted. A single inner `NULL` makes the outer predicate `UNKNOWN` for every row, returning empty. `NOT EXISTS` sidesteps it.

> **Common Pitfall**
>
> `region NOT LIKE 'ap-%'` in the outer `WHERE` drops APAC rows but keeps the service if it has any non-APAC rows. `MAX` and `AVG` then run on a partial slice of a service that does touch APAC.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Rewrite the exclusion with `NOT EXISTS` and explain why. _(Probes naming the `NOT IN` plus `NULL` issue and the null-safe variant.)_
- What changes if the ask is services never billed in APAC in the last 30 days? _(Tests scoping the exclusion subquery without leaking the date filter outward.)_
- How do you spot regions like `apac-tokyo-1` or `cn-ap-1` that `LIKE` misses? _(Forces a conversation about assumed AWS region code shape.)_

## Related

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