# Where the Money Went > Allocated on paper. Spent for real. Reconcile the two. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: LinkedIn ## 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, keeping only the services whose reconciled total tops 10,000. ## Tables ### cloud_costs - cost_id INTEGER - provider TEXT - svc_name TEXT - region TEXT - amount REAL - acct_id TEXT - bill_date TEXT Sample rows (10 of 200): | cost_id | provider | svc_name | region | amount | acct_id | bill_date | | --- | --- | --- | --- | --- | --- | --- | | 543 | GCP | S3 | us-west-2 | 19.33 | acct-1001 | 2026-02-01 | | 586 | Azure | RDS | eu-west-1 | 37.16 | acct-1002 | 2026-03-01 | | 629 | aws | Lambda | ap-southeast-1 | 54.99 | acct-1003 | 2026-04-01 | | 672 | gcp | BigQuery | us-central1 | 72.82 | acct-1004 | 2026-05-01 | | 715 | AWS | Cloud Run | europe-west1 | 90.65 | acct-1005 | 2026-06-01 | | 758 | GCP | Blob Storage | us-east-1 | 108.48 | acct-1006 | 2026-07-01 | | 801 | Azure | Cosmos DB | us-west-2 | 126.31 | acct-1007 | 2026-08-01 | | 844 | aws | CloudFront | eu-west-1 | 144.14 | acct-1008 | 2026-09-01 | | 887 | gcp | Pub/Sub | ap-southeast-1 | 161.97 | NULL | 2026-01-01 | | 930 | AWS | EC2 | us-central1 | 179.8 | acct-1010 | 2026-02-01 | ### cost_allocs - alloc_id INTEGER - team_name TEXT - svc_name TEXT - region TEXT - amount REAL - period TEXT - acct_id TEXT - category TEXT Sample rows (10 of 200): | alloc_id | team_name | svc_name | region | amount | period | acct_id | category | | --- | --- | --- | --- | --- | --- | --- | --- | | 129 | data-eng | S3 | us-west-2 | 83.21 | 2026-02 | acct-101 | storage | | 158 | ml-team | RDS | eu-west-1 | 156.42 | 2026-03 | acct-102 | network | | 187 | frontend | Lambda | ap-south-1 | 229.63 | 2026-04 | acct-103 | database | | 216 | backend | BigQuery | us-central1 | 302.84 | 2026-05 | acct-104 | ml | | 245 | devops | CloudFront | europe-west1 | 376.05 | 2026-06 | acct-105 | other | | 274 | security | EKS | ap-southeast-1 | 449.26 | 2026-07 | acct-106 | compute | | 303 | mobile | SQS | eu-central-1 | 522.47 | 2026-08 | acct-107 | storage | | 332 | Platform | EC2 | us-east-1 | 595.68 | 2026-09 | NULL | network | | 361 | DATA-ENG | S3 | us-west-2 | 668.89 | 2026-01 | acct-109 | database | | 390 | platform | RDS | eu-west-1 | 742.1 | 2026-02 | acct-110 | ml | ### batch_jobs - job_id INTEGER - job_name TEXT - status TEXT - rows_done INTEGER - started TEXT - ended TEXT - priority INTEGER - retries INTEGER Sample rows (10 of 200): | job_id | job_name | status | rows_done | started | ended | priority | retries | | --- | --- | --- | --- | --- | --- | --- | --- | | 1047 | user_export | running | 97 | 2026-02-02 01:00:00 | 2026-02-02 03:11:00 | 3 | 0 | | 1094 | data_cleanup | failed | 194 | 2026-03-03 02:00:00 | 2026-03-03 04:22:00 | 6 | 0 | | 1141 | email_blast | queued | 291 | 2026-04-04 03:00:00 | 2026-04-04 05:33:00 | 9 | 0 | | 1188 | index_rebuild | canceled | 388 | 2026-05-05 04:00:00 | NULL | 2 | 0 | | 1235 | cache_warm | Completed | 485 | 2026-06-06 05:00:00 | 2026-06-06 07:55:00 | 5 | 0 | | 1282 | log_archive | FAILED | NULL | 2026-07-07 06:00:00 | 2026-07-07 08:06:00 | 8 | 0 | | 1329 | metric_agg | completed | 679 | 2026-08-08 07:00:00 | 2026-08-08 09:17:00 | 1 | 0 | | 1376 | backup_db | running | 776 | 2026-09-09 08:00:00 | NULL | 4 | 0 | | 1423 | sync_elastic | failed | 873 | 2026-01-10 09:00:00 | 2026-01-10 11:39:00 | 7 | 5 | | 1470 | daily_report | queued | 970 | 2026-02-11 10:00:00 | 2026-02-11 12:50:00 | 0 | 0 | ## Expected output First 4 of 4 rows: | svc_name | prorated_cost | | --- | --- | | BigQuery | 12997.878727214611 | | EC2 | 11479.357435616439 | | Lambda | 24099.861013755708 | | RDS | 16495.44568415525 | ## 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, kept only for the services whose reconciled total clears a materiality bar. 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, then filter on the reconciled total Aggregate per service with GROUP BY `svc_name` and SUM(...), then keep only the services whose reconciled total tops 10,000 via HAVING SUM(...) > 10000. The threshold is what makes the filter bite: the larger services clear it and the smaller ones fall away, so a query that drops the HAVING returns extra services and grades wrong. --- ### 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) > 10000; ``` > **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 materiality filter on the reconciled total 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/over_budget_services) - [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.