# The Weight of the Cloud > Not every dollar counts the same. Close the quarter. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Apple ## Problem Only bills dated in 2026 belong in this close, and since the 2026 books in `cloud_costs` run only through September there are three calendar quarters to fill. The finance team weights each of those bills by every allocation in `cost_allocs` booked against the same service, whatever period the allocation fell in; report each quarter's total weighted spend as an unrounded decimal. ## 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 | -120.5 | 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 | ## Expected output First 3 of 3 rows: | quarter | weighted_spend | | --- | --- | | Q1 | 4311722773.2458 | | Q2 | 3148557719.7053 | | Q3 | 4319401574.6013 | ## Worked solution and explanation ### What this really is Strip the finance language away and this is a weighted rollup with three quiet ways to go wrong. Each cost has to carry the full allocation total of its service, each bill has to land in the right calendar quarter, and only this year's bills count. Anyone can write the join. What separates candidates is keeping every allocation (match on `svc_name` alone, never tidied with `region` or `acct_id`), fencing the year on the bill side, and remembering that `bill_date` is TEXT, so its month must become a number before `BETWEEN` can bucket it. Get the match wrong and the quarters come out billions low; skip the fence and older bills inflate every quarter and conjure a fourth; skip the cast and every bill piles into one bucket. None of these throws an error. --- ### Walking the query #### Step 1: Match costs to their allocations on `svc_name` Match `cloud_costs` to `cost_allocs` on `svc_name` and nothing else. S3 has two allocation rows, so every S3 cost appears twice, once per allocation. That duplication is the weighting, not a bug to squash. #### Step 2: Fence the costs to 2026 `cloud_costs` spans several years. Keep `strftime('%Y', cc.bill_date) = '2026'` on the cost side so no other year's bills land in these buckets. The allocation `period` is a booking month, not a bill date; filtering it too drops valid pairs. #### Step 3: Cast the month, then bucket it `strftime('%m', ...)` hands back '04', not 4. Without the `CAST(... AS INTEGER)`, the string never satisfies `BETWEEN 4 AND 6` and every row slides into the `ELSE`. January to March is Q1, April to June is Q2, and since the 2026 books are open only through September, everything after June is Q3. #### Step 4: Weight and total `SUM(cc.amount * ca.amount)` per quarter. Because the match already fanned out, this equals each cost times the sum of every allocation booked against its service. Report it unrounded, as finance asked. --- ### The solution **Bucketed weighted spend per quarter** ```sql SELECT CASE WHEN CAST(strftime('%m', cc.bill_date) AS INTEGER) BETWEEN 1 AND 3 THEN 'Q1' WHEN CAST(strftime('%m', cc.bill_date) AS INTEGER) BETWEEN 4 AND 6 THEN 'Q2' ELSE 'Q3' END AS quarter, SUM(cc.amount * ca.amount) AS weighted_spend FROM cloud_costs cc JOIN cost_allocs ca ON cc.svc_name = ca.svc_name WHERE strftime('%Y', cc.bill_date) = '2026' GROUP BY quarter ``` > **The duplication is the weighting** > > There are two honest routes to the weighting: fan the match out on `svc_name` and let `SUM(cc.amount * ca.amount)` add across the duplicated rows, or pre-sum `ca.amount` per service and multiply once. Both are correct. What breaks the metric is deduping: a `DISTINCT` or a pick-one-allocation match throws allocations away and silently undercounts. > **A missing fence or a missing cast fails silently** > > Drop the `strftime('%Y', cc.bill_date) = '2026'` fence and older bills pour into these buckets, including a fourth quarter this year has not reached. Skip the `CAST` and a text month compared to integer bounds never matches, so the whole year lands in Q3. Both hand back plausible wrong numbers. **Naive: tidy the match** A `DISTINCT` or a pick-one-allocation subquery keeps a single allocation per service, or an extra `cc.region = ca.region` predicate trims the pairs. Either way S3's second allocation never touches the weight and the totals come out low. **Correct: keep every allocation** Join on `svc_name` alone and `SUM(cc.amount * ca.amount)` over the fanned-out rows, or pre-sum `ca.amount` per service and multiply once. Each cost is weighted by the full sum of its service's allocations. > **Pre-summing tames the fan-out at scale** > > With `cloud_costs` at 12,000,000 rows and `cost_allocs` at 15,000,000, a fan-out on only 300 services explodes intermediate rows. Pruning `bill_date` to one year's partitions first, then pre-summing allocations per service, turns a billions-row product into a 300-row lookup. > **Say the grain before you write the join** > > Naming the grain out loud before writing the join, and explaining why a pre-summed allocation reaches the same number, is the tell that you understand join grain rather than fearing duplicates. --- ## Common follow-up questions - If someone double-booked an allocation row for a service, how would that show up in your weighted spend, and how would you detect it? _(Tests whether the candidate can reason about join grain and the effect of extra allocation rows on the weighted total.)_ - A service in `cloud_costs` has no row in `cost_allocs`. Should its cost appear in the quarter total, and how would you change the query if so? _(Tests handling of unmatched costs and the inner-versus-outer join decision.)_ - Finance now wants the full year and the same report across three years. How do the quarter labels and the `CASE` need to change? _(Tests whether the bucketing generalizes once the fourth quarter and later years arrive.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_weight_of_the_cloud) - [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.