Cost Density Extremes

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

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.

Some regions pack more cost per node than others.

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 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.

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.

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.)