Beyond the Pacific

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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 never billed in an Asia-Pacific region. Limit it to services whose charges average at least 800, and return each service with its top charge.

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

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

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 billed in an Asia-Pacific region' 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 one at a time instead of excluding the whole service leaves you pricing a partial slice of a service that DOES operate in Asia-Pacific. Get either wrong and the numbers look plausible while describing 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 silently drop rows. Drop the case fold and every 'AWS' and 'GCP' row disappears.

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

Asia-Pacific regions are spelled two ways across clouds: AWS uses an ap- prefix (ap-southeast-1) and GCP uses asia- (asia-northeast1), so match both with region LIKE 'ap-%' OR region LIKE 'asia-%'. Collect the offending services as DISTINCT svc_name in a subquery, then drop them wholesale with NOT IN. The exclusion has to be per-service, not per-row: one Asia-Pacific charge disqualifies the entire service.

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) >= 800. That average gate is what drops a service like 'Blob Storage' that clears the other filters but runs cheaper on average.

The solution

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 region LIKE 'ap-%' OR region LIKE 'asia-%')
GROUP BY svc_name
HAVING AVG(amount) >= 800

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 third cloud names its Tokyo region `jp-east-1`. How do you keep the Asia-Pacific match honest as providers adopt different region-naming schemes? (Forces a conversation about how many region-naming schemes a prefix match has to anticipate, and whether a region lookup table beats a growing list of prefixes.)