Bargains and Budget-Busters

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Problem

FinOps is auditing cloud spend region by region. Two systems record line items: the provider's cost ledger and the team's internal allocations. Treat them as one combined stream of line items. For each region, name the service behind the single biggest line item and the service behind the single smallest. Return one row per region with the region, its biggest spender, and its cheapest line item.

Every region has both. Find them.

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

Why this problem exists in real interviews

Two tables, one combined stream, two extreme picks per group. The interviewer is checking three things: that you UNION ALL the heterogeneous sources rather than treating one as canonical, that you encode the alphabetical tie-break the prompt specifies into the window's ORDER BY rather than letting the engine pick, and that you reach for FIRST_VALUE within a region partition instead of aggregating away the underlying line items.

Break down the requirements

Step 1: Combine the two sources

UNION ALL cloud_costs and cost_allocs, projecting just region, svc_name, amount from each. The two tables record line items from different sources (provider-billed vs. internally allocated), so a row in one is not a duplicate of the other; UNION ALL preserves every line item, while UNION would deduplicate identical (region, svc, amount) tuples and silently lose data.

Step 2: Compute the two extremes per region with FIRST_VALUE

Inside a CTE, attach two window-function columns: FIRST_VALUE(svc_name) OVER (PARTITION BY region ORDER BY amount DESC, svc_name ASC) and FIRST_VALUE(svc_name) OVER (PARTITION BY region ORDER BY amount ASC, svc_name ASC). The secondary svc_name ASC makes the result deterministic when two line items in a region share the extreme amount; without it, FIRST_VALUE returns whichever row the engine sees first. Each row in the CTE now carries its region's most_expensive and cheapest service names.

Step 3: Collapse to one row per region

SELECT DISTINCT region, most_expensive, cheapest collapses the row count to one per region, since the two window-derived columns are constant within a region partition.

The solution

WITH combined AS (
    SELECT region, svc_name, amount FROM cloud_costs
    UNION ALL
    SELECT region, svc_name, amount FROM cost_allocs
),
ranked AS (
    SELECT
        region,
        svc_name,
        amount,
        FIRST_VALUE(svc_name) OVER (PARTITION BY region ORDER BY amount DESC, svc_name ASC) AS most_expensive,
        FIRST_VALUE(svc_name) OVER (PARTITION BY region ORDER BY amount ASC,  svc_name ASC) AS cheapest
    FROM combined
)
SELECT DISTINCT region, most_expensive, cheapest
FROM ranked

Common follow-up questions

• How would you sanity-check that `cloud_costs` and `cost_allocs` aren't both recording the same underlying spend before treating them as one combined stream? (Tests whether the candidate spots that the two sources cover overlapping spend. If allocations are a re-cut of the same dollars in the cost ledger, UNION ALL double-counts. Strong answer: confirm the source semantics with the data owner before unioning.)
• How would the query change if the team wanted the most and least expensive services by total spend, not by single line item? (Tests reframing to per-service totals.)
• How would you rewrite this with `ROW_NUMBER` and a WHERE filter instead of `FIRST_VALUE` plus DISTINCT? (Tests reusing the same CTE shape with `ROW_NUMBER` instead.)