# Yesterday's Weather

> The forecast was off. By how much?

Canonical URL: <https://datadriven.io/problems/yesterday_s_weather>

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

A FinOps team benchmarks its cost models against the simplest possible forecast: whatever a month actually cost becomes next month's prediction. Total each month's real cloud spend, keeping only positive charges so that credits (posted as a negative `amount`) and unpriced items (a null `amount`) never distort the figure, and treat the prior month's total as the current month's forecast. Report each month's year-month, its actual cost, the forecast, and the absolute percent error of the forecast measured against that month's actual cost.

## Worked solution and explanation

### What this really is

Strip the FinOps costume and this is a self-join across time: every month graded against the one before it. The rollup and the prior-month lookup are the easy part, and anyone can write them. Candidates bleed points in two places. First the filter: credits post as a negative `amount` and unpriced rows post as null, so if you sum them in you quietly understate whatever month they land in, and that one bad month then drags its neighbor's percent error into nonsense. Second the denominator: the error is measured against the actual cost, not the forecast. Divide by the wrong one and every number looks plausible and is wrong.

---

### Break down the requirements

#### Step 1: Bucket bill_date by year-month and SUM positive amounts

strftime('%Y-%m', bill_date) gives the YYYY-MM key. SUM(amount) WHERE amount IS NOT NULL AND amount > 0 keeps refunds and zero rows out of the actual cost. Filter in WHERE because amount is a row-level column, not an aggregate.

#### Step 2: LAG to get 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 very first month, which has no prior to compare against.

#### Step 3: Guard pct_error against divide-by-zero

Wrap the percent in CASE WHEN total_amount > 0 THEN ABS((prev_amount - total_amount) / total_amount) * 100 END. If actual cost is zero, pct_error is NULL rather than a SQL error or infinity. ABS makes the error symmetric for over- and under-forecast.

---

### The solution

**Monthly aggregate, LAG forecast, guarded percent error**

```sql
WITH monthly AS (
  SELECT strftime('%Y-%m', bill_date) AS ym, SUM(amount) AS total_amount
  FROM cloud_costs WHERE amount IS NOT NULL AND amount > 0
  GROUP BY strftime('%Y-%m', bill_date)
),
ratios 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,
  CASE WHEN total_amount > 0 THEN ABS((prev_amount - total_amount) / total_amount) * 100 END AS pct_error
FROM ratios
WHERE prev_amount IS NOT NULL
ORDER BY ym
```

> **Cost Analysis**
>
> cloud_costs has 20M rows; the aggregate collapses to roughly 24 to 36 monthly buckets so LAG and the final SELECT are trivial. The full scan with the WHERE filter is the dominant cost; if bill_date is indexed and amount is denormalized cheaply, the planner can skip the scan.

> **Interviewers Watch For**
>
> Did you SUM only positive amounts (refunds would distort the actual), use LAG as a naive forecast (not a window AVG), and CASE-guard the divide-by-zero? Candidates often divide by prev_amount instead of total_amount; the prompt explicitly says the error is measured against actual cost.

> **Common Pitfall**
>
> Dividing by prev_amount looks symmetric but reverses the meaning of the metric: the prompt says the error is relative to the actual cost, so the denominator is total_amount, not the forecast. Read the formula carefully and follow it literally.

---

## 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 formula stays the same; only the forecast definition changes.)_
- How do you handle a missing month (no bills at all)? _(It silently disappears from the aggregate, so LAG compares non-adjacent months. Generate a calendar CTE with recursive WITH and LEFT JOIN to fill zero or NULL gaps before LAG.)_
- Should you exclude the current (incomplete) month? _(Yes: an incomplete month under-reports actual and inflates pct_error. Add WHERE bill_date < strftime('%Y-%m-01', 'now') to scope to fully closed months.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/yesterday_s_weather)
- [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.