Shadow Spend

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

Problem

Billed costs sit in cloud_costs and internal allocations in cost_allocs: what is the total spend for each region and service pairing when a line item (the same region, service and amount) recorded more than once anywhere in the two counts only once? A line item with no amount recorded is not spend, so it never puts a pairing on the list.

Region by region. Service by service. Where does the money go?

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

Strip the FinOps costume and this is a set union with two decisions hiding in the wording. Two feeds describe the same spend, and the same charge can be written down more than once, so the operator that collapses identical rows (UNION) versus the one that keeps every copy (UNION ALL) is the whole ballgame: reach for UNION ALL out of the usual speed reflex and every charge recorded twice is summed twice. The second decision is quieter. A slice of the billed feed carries no amount at all, and if you let those rows through, GROUP BY region, svc_name with SUM(amount) mints a pairing whose total is NULL: a service that looks reported but carries no spend. Everyone can stack two SELECTs. The tell is hearing 'once' demand UNION and dropping the unpriced rows before they become a group.

Break down the requirements

Step 1: Union the two feeds

Select region, svc_name, amount from both cloud_costs and cost_allocs, joined with UNION (not UNION ALL) so a line item recorded more than once collapses to a single row.

Step 2: Drop the unpriced rows

Apply amount IS NOT NULL to the combined set. A row with no amount would otherwise survive the merge and, once grouped, surface as a pairing whose SUM(amount) is NULL: a service that reads as reported yet carries no cost.

Step 3: Aggregate

GROUP BY region, svc_name with SUM(amount) to get one total per pairing.

The solution

WITH combined AS (
    SELECT region, svc_name, amount FROM cloud_costs
    UNION
    SELECT region, svc_name, amount FROM cost_allocs
)
SELECT region, svc_name, SUM(amount) AS total_spend
FROM combined
WHERE amount IS NOT NULL
GROUP BY region, svc_name

Common follow-up questions

• What defines a 'duplicate line item' across the two feeds? (Tests whether deduplication should be on all columns or a subset. UNION deduplicates on every selected column.)
• How would performance change with `UNION ALL` and a separate `DISTINCT` step? (Tests understanding of query plan differences: UNION sorts during the merge; a separate DISTINCT sorts after.)
• What if a line item recorded an `amount` of 0 rather than leaving it blank? (Tests distinguishing a missing value from a real zero before filtering.)