Two Sides of the Ledger

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Uber

Problem

Reconciling each region's budget, an analyst keeps one monthly ledger where every charge in cloud_costs draws the region's balance down and every allocation in cost_allocs builds it back up, each booked in whole dollars with the cents dropped. Charges are stamped with a full billing date while allocations are recorded by month, so each charge counts toward its billing month. For every region and month, show the net movement and the balance standing after it, regions alphabetically with each region's months earliest first.

Two cost tables, one region. Which way does the balance tip?

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 FinOps costume and this is a signed running total over two differently shaped tables, reconciled on a monthly grid. Everyone writes the UNION ALL and the window; what separates candidates is the sign. cloud_costs.amount is stored positive but is money leaving the business, so it is negated before it enters the sum, while cost_allocs.amount stays positive. Miss that and the balance only climbs, quietly hiding every regional deficit. The second silent killer is the partition: drop PARTITION BY region and one region's balance bleeds into the next, collapsing every timeline into one meaningless line.

Build it in four moves

Step 1: Book each movement as signed whole dollars

From cloud_costs take -CAST(amount AS INTEGER), and from cost_allocs take CAST(amount AS INTEGER). The cast drops the cents toward zero, so '19.33' books as 19 and the '-120.5' clawback books as -120. Do it per row, before any sum: the ledger books each charge in whole dollars, not each month.

Step 2: Put charges on the allocation calendar

SUBSTR(bill_date, 1, 7) turns '2026-02-01' into '2026-02', the same shape as period, so a charge and an allocation in the same month share one key. Stack the two sources with UNION ALL, never UNION: two equal movements in one region and month are both real money.

Step 3: Net each region and month

GROUP BY region, event_date with SUM(amt) collapses every booking into one net movement per region and month. That makes each output row unique on its keys, which is what gives the running balance a well-defined order.

Step 4: Carry the balance forward per region

SUM(amt) OVER (PARTITION BY region ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) restarts at each region and adds every month up to the current one. Sort by region, then event_date, so each region's timeline reads top to bottom.

WITH ledger_events AS (
    SELECT region,
           -CAST(amount AS INTEGER) AS amt,
           SUBSTR(bill_date, 1, 7) AS event_date
    FROM cloud_costs
    UNION ALL
    SELECT region,
           CAST(amount AS INTEGER) AS amt,
           period AS event_date
    FROM cost_allocs
),
monthly_net AS (
    SELECT region, event_date, SUM(amt) AS amt
    FROM ledger_events
    GROUP BY region, event_date
)
SELECT region,
       event_date,
       amt,
       SUM(amt) OVER (
           PARTITION BY region
           ORDER BY event_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM monthly_net
ORDER BY region, event_date

Drop cents per booking

SUM(CAST(amount AS INTEGER)): every charge loses its own cents, which is how the ledger books it. Three charges of '10.60' net to 30.

Drop cents per month

CAST(SUM(amount) AS INTEGER): the cents pile up before the cut. The same three charges net to 31, and the error compounds month over month in the balance.

Common follow-up questions

• How would you give each service inside a region its own running balance? (Carry `svc_name` through both union branches and add it to the grouping and to the window partition.)
• Regions like 'ap-south-1' appear only in `cost_allocs`. What does their timeline look like, and what would a FULL view of all regions need? (Tests reading the union: only inflows, so the balance climbs from zero with no dips.)
• How would you produce a day-level balance while still folding each month's allocation in on the first of the month? (Keep `bill_date` whole and map `period` to its first day so both land on a daily key.)