# Cost Density Extremes > Some regions pack more cost per node than others. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Deloitte ## Problem Every billing month in `cloud_costs` has closed except the newest, which is still accruing and stays out of the FinOps team's regional review of cost density. A provider's density in a region is its spend there divided by how many different services show up on its bill there, including services whose amounts were never recorded, rounded to the nearest whole number, and provider spellings that differ only in letter case are one provider, reported in lower case. For every region, show the providers at the lowest and the highest density, with region, provider and density. ## Tables ### cloud_costs - cost_id INTEGER - provider TEXT - svc_name TEXT - region TEXT - amount REAL - acct_id TEXT - bill_date TEXT Sample rows (10 of 200): | 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 First 5 of 12 rows: | region | provider | cost_density | | --- | --- | --- | | ap-southeast-1 | aws | 3429 | | ap-southeast-1 | gcp | 6590 | | eu-west-1 | aws | 3988 | | eu-west-1 | azure | 5609 | | us-central1 | gcp | 4384 | ## Worked solution and explanation ### What this really is This is a per-region extremes problem dressed up as a FinOps review: compute a metric per region and provider, then hold each of those rows up against bounds that only exist one level up, per region. Everyone gets the first `GROUP BY` right. What separates candidates is realizing a bare `MIN()` by region tells you the cheapest number but not whose number it is, so the bounds have to be joined back onto the density rows. Two quieter leaks come first: group on `provider` as stored and 'AWS' and 'aws' compete as two half-providers, and count billing lines instead of unique services and the wrong provider lands at an end. ### Build it in four moves #### Step 1: Drop the open month and fold the spellings The newest month is still accruing, and the table is the only place that knows which month that is. `(SELECT MAX(bill_date) FROM cloud_costs)` returns one value, so `bill_date <` it drops into the `WHERE` as a constant. In the same `closed_costs` CTE, `LOWER(provider)` turns 'AWS' and 'aws' into one provider before anything groups on it, which also gives the lower-case names the output shows. #### Step 2: Compute each pair's density in `density` Group `closed_costs` by `region, provider` and divide `SUM(amount)` by `COUNT(DISTINCT svc_name)`, wrapped in `ROUND()`. A service bills many line items, so the unique count is the only divisor that means 'services'. After folding, each region holds three providers, and aws and gcp each bill two services there. #### Step 3: Take each region's bounds from `density` itself `region_bounds` reads `density` and groups by `region` alone, emitting `low_density` and `high_density`. This is the number without its owner, which is exactly why it cannot be the answer by itself. #### Step 4: Join back and keep either end Join `density` to `region_bounds` on `region` and keep a row when `cost_density` equals either bound. The `OR` keeps every tied provider, and a region with a single provider group matches both bounds on the same row, so it appears exactly once. **Closed months, folded providers, density, then each row against its region's bounds** ```sql WITH closed_costs AS ( SELECT region, LOWER(provider) AS provider, svc_name, amount FROM cloud_costs WHERE bill_date < (SELECT MAX(bill_date) FROM cloud_costs) ), density AS ( SELECT region, provider, ROUND(SUM(amount) / COUNT(DISTINCT svc_name)) AS cost_density FROM closed_costs GROUP BY region, provider ), region_bounds AS ( SELECT region, MIN(cost_density) AS low_density, MAX(cost_density) AS high_density FROM density GROUP BY region ) SELECT d.region, d.provider, d.cost_density FROM density AS d JOIN region_bounds AS b ON b.region = d.region WHERE d.cost_density = b.low_density OR d.cost_density = b.high_density ``` **Row-count divisor** `SUM(amount) / COUNT(*)` divides by every line item. The aws and gcp groups carry far more lines than services, so in us-west-2 gcp lands at the low end. **Unique-service divisor** `SUM(amount) / COUNT(DISTINCT svc_name)` divides by the two services each folded group actually runs, so the number reads as spend per service and aws holds the low end in us-west-2. > **Round once, compare the same number** > > Rounding happens inside `density`, before `region_bounds` reads it. Round in one place and not the other and the equality in the final `WHERE` silently fails for rows that should match. > **A missing amount still bills a service** > > `SUM(amount)` skips NULL amounts, but `COUNT(DISTINCT svc_name)` still counts the service. In us-east-1 aws's EC2 lines carry no amount at all, yet EC2 is on the bill, so the divisor is 2. Filter out NULL amounts first and that group's density doubles and the region's low end changes. > **The scope lives in the join key** > > Candidates who take `MIN` and `MAX` over the whole of `density` get two rows for the entire fleet. 'Every region' is the requirement, and it lives in grouping `region_bounds` by `region` and joining on it. > **One pass, then a few dozen rows** > > `bill_date` is the partition key across 36 months, so excluding the open month skips one partition and the scan reads the other 35 in a single pass. `MAX(bill_date)` is answered from partition metadata or one cheap lookup, and `density` collapses the table to a few dozen rows, so the join back is free. > **Fold the key before anything groups on it** > > Provider arrives as 'AWS' and 'aws' because upstream writers disagree on casing. Group as stored and each half of one vendor's bill competes against the other: us-central1's high end would be 'AWS' alone. Fix casing at ingestion in a real pipeline, but never let a review query trust that it was fixed. ## Common follow-up questions - How would you label each returned row as the region's 'low' or 'high' end? _(Tests a `CASE` over `low_density` and `high_density`, and what to print when both match.)_ - What if the review wanted only the last three closed months? _(Tests bounding `bill_date` on both sides of the scalar `MAX(bill_date)`, and whether density is per month or pooled.)_ - How would you report each provider under its most common original spelling instead of lower case? _(Tests grouping on `LOWER(provider)` while choosing a display spelling per group.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/cost_density_extremes) - [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.