Where the Money Went

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

LinkedIn

Problem

We reconcile each team's budget allocation in cost_allocs against the actual charge in cloud_costs for the same service in the same billing month, prorating every pairing to an hourly figure by multiplying the allocated amount by the actual cost and dividing by the hours in a year. Return each service with its total prorated cost, keeping only the services whose reconciled total tops 10,000.

Allocated on paper. Spent for real. Reconcile the two.

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

batch_jobs

Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER

Expected output

Worked solution and explanation

What this really is

Underneath the cost-accounting story, this is a join on a key that does not exist yet. An allocation's period reads '2026-02' while a charge's bill_date reads '2026-02-01', so a straight equality between them matches nothing: you have to manufacture the month from the date before the join can fire at all. After that it is a plain sum of a per-row product, kept only for the services whose reconciled total clears a materiality bar. The schema also hands you batch_jobs, which shares no meaningful key with either cost table: joining it buys nothing, and if its key fanned out it would silently multiply every total. Get the month wrong and you get zero rows; drag in the wrong table and you cloud a clean aggregate.

Break down the requirements

Step 1: Derive the month, then join on service

The join key is a month that is not stored as a month. cost_allocs.period is year-month ('2026-02') while cloud_costs.bill_date is a full date ('2026-02-01'). Derive the month with strftime('%Y-%m', cc.bill_date) and match it to ca.period, together with svc_name. Leave region out of the key: the two tables disagree on it for the same service and month. A raw bill_date = period equality returns zero rows, the most common way this problem dies.

Step 2: Recognize the distractor table

The schema also offers batch_jobs, but job_id has no relationship to an allocation or a charge, and none of its columns feed the reconciliation. Joining it adds nothing, and if job_id were non-unique the extra rows would multiply into the sum and inflate every total. The disciplined move is to leave it out entirely.

Step 3: Prorate each pairing

Multiply the allocated amount by the actual cloud cost and divide by the hours in a year (8760 = 365 * 24): ca.amount * cc.amount / 8760.0. Write 8760.0 so the division is floating point on an engine that would otherwise truncate integers.

Step 4: Aggregate, then filter on the reconciled total

Sum per service with GROUP BY ca.svc_name, then keep only services whose total tops 10,000 with HAVING SUM(...) > 10000. The threshold bites on the seed: the larger services clear it and the smaller ones fall away, so dropping the HAVING returns extra services and grades wrong.

The solution

SELECT
    ca.svc_name AS svc_name,
    SUM(ca.amount * cc.amount / 8760.0) AS prorated_cost
FROM cost_allocs ca
INNER JOIN cloud_costs cc
    ON ca.svc_name = cc.svc_name
    AND ca.period = strftime('%Y-%m', cc.bill_date)
GROUP BY ca.svc_name
HAVING SUM(ca.amount * cc.amount / 8760.0) > 10000;

Common follow-up questions

• Suppose a stakeholder insists on attaching `batch_jobs` context and `job_id` turns out to be non-unique. How would that join change your `prorated_cost` totals, and how would you defend the aggregate? (Tests reasoning about join fan-out at a different grain.)
• Why does the materiality filter on the reconciled total belong in `HAVING` rather than `WHERE` here? (Tests pre-aggregation versus post-aggregation filtering.)
• The join matches `ca.period` against a month derived from `bill_date`. What would you change so the optimizer can use an index for that predicate at 15 million rows? (Tests indexing for a join key computed from an expression.)