# Two Sides of the Ledger > Two cost tables, one region. Which way does the balance tip? Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem The FinOps team reconciles each region's cloud charges against its budget allocations on a single monthly timeline, where a charge draws the region's balance down and an allocation builds it back up. Charges are stamped with a full billing date while allocations are recorded by month, so line each charge up on its month before combining the two sources. For every region and month, show the net movement and the balance standing after it, earliest month first within each region. ## Worked solution and explanation ### Why this problem exists in real interviews Strip the FinOps costume and this is a signed running total over a UNION of two differently shaped tables, reconciled on a monthly grid. Everyone writes the UNION and the window function; what separates candidates is the sign. cloud_costs.amount is stored positive, but it is money leaving the business, so it has to be negated before it enters the sum, while cost_allocs.amount stays positive. Miss that one negation and the balance only ever climbs, quietly hiding every regional deficit the owner actually needs to see. The second silent killer is the partition: drop PARTITION BY region and one region's balance bleeds into the next, collapsing twelve timelines into one meaningless line. --- ### Break down the requirements #### Step 1: UNION ALL the two sources into one signed monthly stream Pull `region`, a signed amount, and an event month from each table. From `cloud_costs` take `-CAST(amount AS DOUBLE)` and truncate `bill_date` to its `YYYY-MM` prefix with `SUBSTR(bill_date, 1, 7)`: `amount` is a positive REAL, but a charge is money leaving the business, so the sign flips. From `cost_allocs` take `CAST(amount AS DOUBLE)` with `period` (already `YYYY-MM`) as the event month, since allocations are positive inflows. The `CAST` keeps both branches on the same numeric type so the sum stays in floating point, and `UNION ALL` is correct because we never want to deduplicate rows across sources. #### Step 2: Net the movements per region and month `GROUP BY region, event_date` and `SUM` the signed amounts so each region and month becomes one net movement. Truncating `bill_date` to its month lines the day-level charges up with the month-level `period`, so within a region and month a charge and its allocation land in the same bucket and net directly. This is also what makes the running balance reproducible: without the netting, several rows share a region and month and the per-row cumulative sum has no defined order. #### Step 3: Run a per-region cumulative SUM `SUM(amt) OVER (PARTITION BY region ORDER BY event_date ROWS UNBOUNDED PRECEDING)` carries the balance forward inside each region. `PARTITION BY region` resets it per region, so a deficit in `us-east-1` never leaks into `eu-west-1`, and the explicit frame avoids engine-specific defaults that fold in peer rows on the same key. #### Step 4: Project the four required columns and sort Project `region, event_date, amt, running_balance`, ordered by `region` then `event_date` so each region's timeline reads top to bottom. Since the nets are unique per region and month, this ordering is fully deterministic. --- ### The solution **UNION ALL with sign convention, align charges to month, net per month, then per-region running SUM** ```sql WITH all_events AS ( SELECT region, -CAST(amount AS DOUBLE) AS amt, SUBSTR(bill_date, 1, 7) AS event_date FROM cloud_costs UNION ALL SELECT region, CAST(amount AS DOUBLE) AS amt, period AS event_date FROM cost_allocs ), monthly AS ( SELECT region, event_date, SUM(amt) AS amt FROM all_events GROUP BY region, event_date ) SELECT region, event_date, amt, SUM(amt) OVER (PARTITION BY region ORDER BY event_date ROWS UNBOUNDED PRECEDING) AS running_balance FROM monthly ORDER BY region, event_date ``` > **Cost Analysis** > > `cloud_costs` and `cost_allocs` are each 15M rows across 36 monthly partitions, so the `UNION ALL` feeds a 30M-row scan. The `GROUP BY region, event_date` collapses that to at most a few hundred rows (12 regions times the distinct months), so the window SUM runs over a tiny result. The cost is dominated by the aggregation scan over the 30M input rows, not the window. > **Interviewers Watch For** > > Interviewers watch whether you negate `cloud_costs.amount` (a positive REAL that semantically represents an outflow) and whether you keep both UNION branches on the same numeric type with `CAST(amount AS DOUBLE)`. They look for the billing date truncated to `YYYY-MM` so day-level charges reconcile against month-level allocations, the `GROUP BY region, event_date` that nets movements before the window so the balance is reproducible, `PARTITION BY region` on the running SUM, and `UNION ALL` (correct) over `UNION` (which would silently drop a real allocation that matched a cost row). > **Common Pitfall** > > Forgetting to negate `cloud_costs.amount` makes the balance climb forever, hiding every deficit. Omitting `PARTITION BY region` accumulates globally across regions, producing one meaningless timeline. Leaving `bill_date` at day granularity keeps charges out of the month bucket their allocation lands in, so they never net together and the timeline splits into stray extra rows. --- ## Common follow-up questions - How would you add `svc_name` as a second key so each service inside a region has its own running balance? _(Tests whether the candidate carries `svc_name` through the UNION and adds it to both the GROUP BY and PARTITION BY without re-aggregating.)_ - What would the query return for a region that appears only in `cost_allocs` and never in `cloud_costs`? _(Tests UNION ALL semantics: only inflow nets appear and the balance climbs monotonically from zero with no negative dips.)_ - The billing date carries day-level detail you are currently discarding. How would you instead produce a day-level running balance while still folding each month's allocation in on the first of that month? _(Tests day-level normalization: keep bill_date at full day granularity and map each period to a concrete day (for example YYYY-MM-01) so allocations slot into the daily timeline.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/two_sides_of_the_ledger) - [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.