Yesterday's Weather

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

L4

Asked in

Uber

Problem

A FinOps team benchmarks its cost models against the simplest possible forecast: whatever a month actually cost becomes next month's prediction. Each row in cloud_costs is a single cloud charge, and credits post as a negative amount, so total each month's real spend from the positive charges only and treat the prior month's total as the current month's forecast. For every month that has a prior month to compare against, report its year-month, its actual cost, the forecast, the absolute percent error of the forecast against that month's actual cost, and whether the actual came in over or under the forecast.

The forecast was off. 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

Strip the FinOps costume and this is a persistence forecast, the 'yesterday's weather' trick: each month is graded against the one immediately before it. Rolling row-level charges up to monthly totals and reaching back one month with an offset is the easy part, and anyone can write it. Candidates bleed points in two places. First the denominator: the error is measured against the actual cost, not the forecast, so the divisor is total_amount, not prev_amount. Divide by the wrong one and every number looks plausible and is wrong. Second the inputs: credits post as a negative amount, so if you sum them in you understate whatever month they land in, and that one bad month drags its neighbor's percent error into nonsense.

Break down the requirements

Step 1: Bucket bill_date by year-month and SUM the positive charges

strftime('%Y-%m', bill_date) gives the YYYY-MM key, and SUM(amount) WHERE amount > 0 keeps credits out of the actual cost. Filter in WHERE, not HAVING, because amount is a row-level column, not an aggregate. Since amount > 0 already rejects both credits and any unpriced row, no separate null test on amount is needed here.

Step 2: LAG to pull the prior month as the naive forecast

LAG(total_amount) OVER (ORDER BY ym) is prev_amount, the forecast. Then WHERE prev_amount IS NOT NULL drops the earliest month, which has no prior total to forecast from and would otherwise emit a row with a null forecast.

Step 3: Score the miss, then label its direction

pct_error is ABS((prev_amount - total_amount) / total_amount) * 100: the actual cost is the denominator and ABS makes an over-forecast and an under-forecast of the same size count equally. A CASE then compares total_amount to prev_amount to tag each month over_forecast or under_forecast, so the magnitude and the direction of the miss sit side by side.

The solution

WITH monthly AS (
  SELECT strftime('%Y-%m', bill_date) AS ym, SUM(amount) AS total_amount
  FROM cloud_costs
  WHERE amount > 0
  GROUP BY strftime('%Y-%m', bill_date)
),
forecast AS (
  SELECT ym, total_amount, LAG(total_amount) OVER (ORDER BY ym) AS prev_amount FROM monthly
)
SELECT
  ym,
  total_amount AS actual_cost,
  prev_amount AS forecasted_cost,
  ABS((prev_amount - total_amount) / total_amount) * 100 AS pct_error,
  CASE
    WHEN total_amount > prev_amount THEN 'over_forecast'
    WHEN total_amount < prev_amount THEN 'under_forecast'
    ELSE 'on_forecast'
  END AS forecast_outcome
FROM forecast
WHERE prev_amount IS NOT NULL

Common follow-up questions

• How would you switch the forecast from naive (last month) to a 3-month moving average? (Replace LAG with AVG(`total_amount`) OVER (ORDER BY ym ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING). The percent error and direction logic stay the same; only the forecast definition changes.)
• How do you handle a month with no bills at all? (It silently drops out of the aggregate, so LAG compares non-adjacent months. Build a calendar CTE with a recursive WITH and LEFT JOIN to fill the gap before LAG.)
• Should you exclude the current, incomplete month? (Yes: a partial month under-reports actual and inflates `pct_error`. Filter `bill_date` to fully closed months before aggregating.)