The Quiet Drain

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

How many different AWS services in each region of cloud_costs have at least one high-cost line item, from the most down to the fewest? The FinOps analyst asking counts any AWS line item of 200 or more as high-cost, however the provider name happens to be capitalized.

Some AWS services quietly drain the budget.

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 is really asking

Under the FinOps framing this is a per-region count of different services, and the whole question is which unit you count. Every qualifying AWS service appears on several line items in cloud_costs (billed in the same region bill after bill), so COUNT(*) reports line items: 6 for 'us-east-1', where only CloudFront qualifies. The second trap is the dirty provider column. The vendor is stored as both 'aws' and 'AWS', and an exact match throws away every EC2 and Cloud Run row. Fold the case before filtering, and count each svc_name once per region.

The two traps hiding in the data

Step 1: Normalize the provider casing

The sample rows carry 'aws' and 'AWS' for the same vendor. WHERE provider = 'aws' keeps only the lowercase spelling, which on this data holds just BigQuery and CloudFront. LOWER(provider) = 'aws' folds both spellings into one filter before anything else runs.

Step 2: Apply the floor, then count services once

Keep entries where amount >= 200 (200 itself is high-cost). That also drops the negative credits and the rows with no amount. Group by region and use COUNT(DISTINCT svc_name) so repeat line items for the same service collapse to one.

Step 3: Sort most to fewest

ORDER BY service_count DESC is the whole order. Four regions tie at 2 and two at 1. The question only asks for most to fewest, so tied regions may come back in any order and no extra sort column is owed.

The solution

SELECT region,
       COUNT(DISTINCT svc_name) AS service_count
FROM cloud_costs
WHERE LOWER(provider) = 'aws'
  AND amount >= 200
GROUP BY region
ORDER BY service_count DESC

Line-item volume

SELECT region, COUNT(*) FROM cloud_costs WHERE provider = 'aws' AND amount >= 200 GROUP BY region counts every qualifying row and misses the 'AWS'-cased ones, so it answers a different question than the one asked.

Different services

COUNT(DISTINCT svc_name) with LOWER(provider) = 'aws' counts services rather than rows and captures both spellings, which is the number FinOps actually wants.

Common follow-up questions

Common follow-up questions

• How would you also show the total spend per region alongside the service count? (Add `SUM(amount)` to the same SELECT; the WHERE and GROUP BY stay put.)
• What changes if the 200 threshold should apply to each service's monthly total rather than a single line item? (Pre-aggregate by `region`, `svc_name` and month in a CTE, filter the aggregate, then count services.)
• How would you scope this to a single billing month so partition pruning kicks in? (Add a `bill_date` range predicate matching the partition key so only that month's partitions are scanned.)