Cloud Cost Stats by Provider

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

L3

Problem

Allocations in cost_allocs carry no provider, so their category stands in as the label, and they count alongside the billed lines in cloud_costs as one set of costs. An analyst reconciling spend wants each provider with its minimum, maximum and average amount.

Three providers. Three very different bills.

Tables

cloud_costs

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

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

Worked solution and explanation

What this really is

This is a vertical stack wearing a reporting costume. Two tables that share nothing but an amount column have to become one list of cost lines, and the only real decision is how their rows line up. Anyone can write MIN, MAX and AVG. What separates candidates is seeing three things: there is no key to join on, cost_allocs has no provider so its category must stand in, and every line has to survive the merge. Reach for a join and each amount is counted once per partner it fans out to. Reach for a deduplicating UNION and genuine repeat charges vanish from the average.

Getting there

Step 1: Notice there is nothing to join on

cloud_costs and cost_allocs share svc_name, region and acct_id, but none of them identifies a row on either side. A join on any of them pairs each billing line with every allocation that shares the value, so the same amount lands in the aggregate many times. The ask is one dataset, which means more rows, not wider ones.

Step 2: Make the two shapes line up

A stack matches columns by position, so each branch selects exactly two: a label and amount. cloud_costs contributes provider. cost_allocs has no such column, so category AS provider puts 'compute', 'storage' and the rest in the same slot. The preview gives this away: 'compute' sits in the provider column next to 'AWS'.

Step 3: Keep every line, then summarise once

UNION ALL appends the second branch without comparing rows, which is both correct and the cheaper option. Name it combined, then GROUP BY provider with MIN(amount), MAX(amount) and AVG(amount). Labels stay as stored, so 'AWS' and 'aws' come out as separate rows, exactly as the preview shows.

WITH combined AS (
    SELECT provider, amount FROM cloud_costs
    UNION ALL
    SELECT category AS provider, amount FROM cost_allocs
)
SELECT
    provider,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount,
    AVG(amount) AS avg_amount
FROM combined
GROUP BY provider

Deduplicating stack

UNION hashes or sorts the whole combined set to drop rows equal on every selected column. Two separate charges of the same amount under the same label become one, and AVG(amount) drifts toward the values that never repeat.

Append-only stack

UNION ALL streams one branch after the other with no comparison. Every billing line and every allocation line reaches the aggregate exactly once, which is what treating both sources as one dataset means.

Common follow-up questions

• What if the two tables recorded `amount` in different currencies? (Tests whether you normalise each branch before stacking rather than after aggregating.)
• How would you also track which source each row came from? (Add a literal per branch, such as 'billing' AS `source` and 'allocation' AS `source`, and include it in the grouping if the split matters.)
• What if `cost_allocs` had no meaningful stand-in for a provider? (Tests whether you pick a placeholder label, leave the label NULL, or exclude the table, and say why.)