# The Revenue Cliff

> Revenue was climbing. Then it wasn't. Spot the drop.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Pull a monthly revenue trend showing the month-over-month percentage change. Flag any month where revenue dropped more than 10% from the previous month. Round to 2 decimal places.

## Worked solution and explanation

### Why this problem exists in real interviews

Finance and ops teams need a single rollup that flags month-over-month revenue drops without a human eyeballing a chart. Interviewers use this to see whether you can cast TEXT money fields, bucket by year-month, use LAG for the prior period, and emit a flag column with the right threshold semantics.

---

### Break down the requirements

#### Step 1: Cast total_amount and bucket by YYYY-MM

Every column on transactions is TEXT, so wrap total_amount in CAST(... AS DOUBLE) before SUM. Use strftime('%Y-%m', transaction_date) to get a sortable month key. Doing both in the base CTE keeps the rest of the query clean.

#### Step 2: Pull prior month with LAG ordered by month

LAG(revenue) OVER (ORDER BY month) gives prev_revenue. The first month's prev_revenue is NULL, which propagates correctly to pct_change and the flag column.

#### Step 3: Compute pct_change and the ALERT flag

pct_change is (revenue - prev_revenue) / prev_revenue * 100, rounded to 2 decimals. The flag is a CASE that returns 'ALERT' when pct_change is strictly less than -10, otherwise an empty string. Empty string, not NULL, on non-alert rows.

---

### The solution

**Monthly aggregate, LAG for prior, CASE for the alert flag**

```sql
WITH monthly AS (
  SELECT strftime('%Y-%m', transaction_date) AS month, SUM(CAST(total_amount AS DOUBLE)) AS revenue
  FROM transactions GROUP BY strftime('%Y-%m', transaction_date)
),
with_lag AS (
  SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM monthly
)
SELECT month,
       ROUND(revenue, 2) AS revenue,
       ROUND(prev_revenue, 2) AS prev_revenue,
       ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS pct_change,
       CASE WHEN (revenue - prev_revenue) / prev_revenue * 100 < -10 THEN 'ALERT' ELSE '' END AS flag
FROM with_lag
ORDER BY month
```

> **Cost Analysis**
>
> transactions has 100M rows but only 365 distinct dates and 12 months in a typical year. The aggregate collapses to a tiny set so the LAG window is cheap. The dominant cost is the SUM(CAST(... AS DOUBLE)) full scan; a covering index on (transaction_date, total_amount) helps if you cannot precompute.

> **Interviewers Watch For**
>
> Did you cast total_amount before SUM, use strftime for the month key, and emit " rather than NULL on non-alert months? A common miss is forgetting that the first month's flag should still be empty string because pct_change is NULL and NULL < -10 is NULL, not true.

> **Common Pitfall**
>
> SUM on a TEXT column in SQLite silently coerces and returns 0 or wrong results when the strings have stray whitespace or currency symbols. Always wrap money TEXT in CAST(... AS DOUBLE) explicitly, and round only at the SELECT, never inside the SUM.

---

## Common follow-up questions

- What if you needed YoY change instead of MoM? _(Replace LAG(revenue) OVER (ORDER BY month) with LAG(revenue, 12) OVER (ORDER BY month). The structure is identical; only the offset changes.)_
- How would you handle months with zero revenue? _(Division by zero in SQLite returns NULL. Wrap pct_change in CASE WHEN prev_revenue = 0 THEN NULL ELSE ... END to keep the intent explicit and avoid silent NULL propagation.)_
- How do you backfill a missing calendar month? _(Generate a calendar CTE with recursive WITH and LEFT JOIN the monthly aggregate. Otherwise gaps in the data become gaps in the output and LAG silently compares non-adjacent months.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_revenue_cliff)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.