Cheapest Cost Per Region
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
- Asked in
- Airbnb
Problem
The FinOps team wants to know the floor price they're paying in each region. Show the minimum cloud cost amount recorded for each region.
Lowest spend per region.
Tables
cloud_costs
Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT
| cost_id | provider | svc_name | region | amount | acct_id | bill_date |
|---|---|---|---|---|---|---|
| 543 | GCP | S3 | us-west-2 | 19.33 | acct-1001 | 2026-02-01 |
| 586 | Azure | RDS | eu-west-1 | 37.16 | acct-1002 | 2026-03-01 |
| 629 | aws | Lambda | ap-southeast-1 | 54.99 | acct-1003 | 2026-04-01 |
| 672 | gcp | BigQuery | us-central1 | 72.82 | acct-1004 | 2026-05-01 |
| 715 | AWS | Cloud Run | europe-west1 | 90.65 | acct-1005 | 2026-06-01 |
| 758 | GCP | Blob Storage | us-east-1 | 108.48 | acct-1006 | 2026-07-01 |
| 801 | Azure | Cosmos DB | us-west-2 | 126.31 | acct-1007 | 2026-08-01 |
| 844 | aws | CloudFront | eu-west-1 | 144.14 | acct-1008 | 2026-09-01 |
| 887 | gcp | Pub/Sub | ap-southeast-1 | 161.97 | NULL | 2026-01-01 |
| 930 | AWS | EC2 | us-central1 | 179.8 | acct-1010 | 2026-02-01 |
Expected output
| region | min_cost |
|---|---|
| ap-southeast-1 | -181.1 |
| eu-west-1 | -137.4 |
| europe-west1 | -268.5 |
| us-central1 | -224.8 |
| us-east-1 | 108.48 |
Worked solution and explanation
This is a per-region floor, and the costume is the word 'price'. Anyone can produce one row per region. What separates candidates is whether they trust the data when five of the six floors come back negative. Those rows are credits and refunds on the bill, and they are the real lows. Add a WHERE amount >= 0 because 'a price cannot be negative', and five regions quietly report an inflated floor. Only 'us-east-1', which never got a credit, survives that edit.
Walk it through
Step 1: Keep one row per region
The ask is 'for each region', so the result has one row per value of region, six on this table. A bare SELECT MIN(amount) FROM cloud_costs answers a different question: one floor for the whole bill, -268.5, with no region attached.
Step 2: Take the low of amount exactly as recorded
MIN(amount) per region, named min_cost. Filter nothing first. The negatives are credits the region really received, and the twelve rows with no amount are already skipped by MIN on its own.
SELECT region, MIN(amount) AS min_cost
FROM cloud_costs
GROUP BY region- Filters the credits
WHERE amount >= 0runs before grouping, so 'eu-west-1' reports a positive floor instead of -137.4. The same happens to every region whose lowest line is a credit.- Reads the bill as it is
- No filter.
MIN(amount)sees every recorded line, so 'europe-west1' lands at -268.5 and 'us-east-1' at 108.48, matching what finance actually booked.
Common follow-up questions
- What if you also needed the `svc_name` that produced each region's floor? (Tests carrying a row's other columns alongside its minimum: a ranking per region, a join back to the floor, or a correlated lookup.)
- Finance now wants the floor of charges only, with credits excluded. What changes? (Tests filtering in `WHERE` once the business rule actually says so, and noticing that every floor moves.)
- What if you needed both the floor and the ceiling per region? (Add `MAX(amount)` next to `MIN(amount)`; tests comfort with several aggregates in one pass.)