# Where the Money Went > Allocated on paper. Spent for real. Reconcile the two. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We reconcile each team's budget allocation against the actual cloud charge 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, highest first, keeping only services whose total comes out positive. ## 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. The allocation's period reads 2026-02 while the 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. The schema also hands you a batch_jobs table that shares no meaningful key with either cost table: joining it in buys you nothing, and if its key were non-unique it would fan out and silently double every total. Get the month derivation 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 YYYY-MM (e.g. 2026-02) while cloud_costs.bill_date is a full date (e.g. 2026-02-01). Derive the month with strftime('%Y-%m', cc.bill_date) and match it to ca.period, together with svc_name. A raw bill_date = period equality returns zero rows, which is the single most common way this problem dies. #### Step 2: Recognize the distractor table The schema also offers batch_jobs, but its job_id has no real relationship to an allocation or a charge, and none of its columns feed the reconciliation. Joining it in adds nothing to the answer, 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 of the query entirely. #### Step 3: Prorate each pairing Prorate each matched pairing by multiplying the allocated amount by the actual cloud cost and dividing by the hours in a year (8760 = 365 * 24): ca.amount * cc.amount / 8760.0. Use 8760.0 so the division is floating point, not integer. #### Step 4: Aggregate, filter, order Aggregate per service with GROUP BY svc_name and SUM(...), keep only services with a positive total via HAVING SUM(...) > 0, and order by the total descending with svc_name as the tie-breaker. --- ### The solution **Two-table join with proration formula** ```sql 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) > 0 ORDER BY prorated_cost DESC, svc_name ASC; ``` > **Cost Analysis** > > The join fires on svc_name AND the derived month, so at 15,000,000 charge rows and 20,000,000 allocation rows the winning plan is a hash join keyed on (svc_name, strftime-month). Because the month is computed, an index on bill_date alone will not be used for the equality; a persisted month column (or a functional index on strftime('%Y-%m', bill_date)) is what lets the optimizer probe the index instead of scanning. At this scale a pre-aggregated materialized view keyed on (svc_name, period) is worth considering. > **Interviewers Watch For** > > The tell of a senior candidate is spotting the period-vs-date format mismatch before writing the join, and recognizing that batch_jobs is a distractor with no meaningful key into either cost table. Candidates who join period to bill_date directly, or who reflexively join every table the schema offers, are the ones this problem is designed to catch. > **Common Pitfall** > > The quiet killer is a join whose key looks like it should match but does not: period is a month, bill_date is a day. The other trap is reflexively enriching with batch_jobs: if a joined table has multiple rows per key, every SUM silently multiplies. Neither error throws; both just return wrong numbers. --- ## Common follow-up questions - Suppose a stakeholder insists on attaching batch_jobs context and its 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 whether the candidate can reason about join fan-out at a different grain.)_ - Why does the positive-cost filter belong in HAVING rather than WHERE here? _(Tests understanding of 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 knowledge for a join key computed from an expression.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_money_went) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.