Cost Efficiency Variance

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

Some months' per-service cost figures swing far more than others, and the finance team's chargeback review wants that spread measured straight from cloud_costs. Take each billing line at its whole-dollar amount with the cents simply dropped, then split it evenly across the number of different services billed in its region to get a decimal cost ratio. For every year-month from the earliest forward, show the average ratio, the month's mean, and the average absolute gap between each line's ratio and that mean.

Cost efficiency varies. By how much?

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

Worked solution and explanation

What this really is

This is a mean absolute deviation computed at two grains, dressed up as a FinOps variance report. The denominator, the number of different services per region, lives at a coarser grain than the billing lines. The deviation needs each month's mean sitting next to every line before you can average the gaps. Anyone can write GROUP BY ym. The candidates who pass are the ones who refuse to collapse it into one pass. Write AVG(ABS(cost_ratio - AVG(cost_ratio))) and the engine rejects the nested aggregate. Fake the mean or count services inline and every avg_abs_difference is measured against the wrong baseline, silently.

There is a second, quieter trap in the types. The spec bills lines in whole dollars, so amount has to become an integer. The moment it does, dividing it by an integer service count truncates the ratio.

Break down the requirements

Step 1: Build the per-region denominator

Count the different svc_name values per region in its own CTE. svc_name repeats across many line items in a region, so COUNT(*) would give you line counts, not service counts. That is why the count needs COUNT(DISTINCT svc_name). The table is tiny: six regions, five services each on the seed.

Step 2: Drop the cents, then keep the division decimal

CAST(cc.amount AS INTEGER) truncates the cents, so 1201.37 bills as 1201 and a credit of -52.3 as -52. That integer divided by the integer svc_count would be integer division and throw away the remainder, so cast the denominator with CAST(sr.svc_count AS REAL). Both casts move the numbers: drop the first and the cents leak back in; drop the second and 1201 / 5 becomes 240 instead of 240.2.

Step 3: Materialize the monthly mean, then measure the gaps

Average cost_ratio per ym in monthly_avg. Join each line back to its month's mean, take ABS(wr.cost_ratio - ma.avg_ratio), then average those gaps per month. Lines with a NULL amount produce a NULL ratio, and AVG skips them in both passes, so the mean and the deviation agree on which lines count.

The solution

WITH svc_per_region AS (
  SELECT region, COUNT(DISTINCT svc_name) AS svc_count
  FROM cloud_costs
  GROUP BY region
),
with_ratio AS (
  SELECT strftime('%Y-%m', cc.bill_date) AS ym,
         CAST(cc.amount AS INTEGER) / CAST(sr.svc_count AS REAL) AS cost_ratio
  FROM cloud_costs cc
  INNER JOIN svc_per_region sr ON cc.region = sr.region
),
monthly_avg AS (
  SELECT ym, AVG(cost_ratio) AS avg_ratio
  FROM with_ratio
  GROUP BY ym
),
diffs AS (
  SELECT wr.ym,
         wr.cost_ratio AS actual_ratio,
         ma.avg_ratio AS monthly_average,
         ABS(wr.cost_ratio - ma.avg_ratio) AS abs_diff
  FROM with_ratio wr
  INNER JOIN monthly_avg ma ON wr.ym = ma.ym
)
SELECT ym,
       AVG(actual_ratio) AS actual_ratio,
       AVG(monthly_average) AS monthly_average,
       AVG(abs_diff) AS avg_abs_difference
FROM diffs
GROUP BY ym
ORDER BY ym

Common follow-up questions

Common follow-up questions

• Could you compute the monthly MAD with `AVG(cost_ratio) OVER (PARTITION BY ym)` and skip the join back? (Checks whether you can broadcast the mean with a window instead of a second CTE and join.)
• If the service count per `region` changes over time, how should the ratio handle that? (Tests grain thinking: should `svc_per_region` also be keyed by `ym`?)
• Finance now wants the cents rounded to the nearest dollar instead of dropped. What changes, and what happens to the credits? (Probes the difference between truncation and rounding on negative amounts.)