The Weight of the Cloud

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

Apple

Problem

The 2026 close takes only bills dated in 2026, and since those books in cloud_costs run only through September there are three calendar quarters to fill. Finance 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 a decimal number at full precision, with no rounding.

Not every dollar counts the same. Close the quarter.

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

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

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

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.

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.)