# The Revenue Cliff > Revenue was climbing. Then it wasn't. Spot the drop. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Finicity ## Problem We're watching our monthly revenue in `transactions` for sharp declines: each `month` ('YYYY-MM'), earliest onward, reports `revenue_thousands`, its total in thousands of dollars to two decimals, and `pct_change`, the change from the month before worked out on the unrounded monthly totals and shown in whole percentage points with the fraction dropped, so a 12.7% fall reads -12. Put 'ALERT' in an `alert` column for any month whose exact change is a fall of more than 10%, and leave `alert` null for every other month. ## Tables ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ## Expected output First 5 of 57 rows: | month | revenue_thousands | pct_change | alert | | --- | --- | --- | --- | | 2022-01 | 0.86 | NULL | NULL | | 2022-02 | 1.53 | 78 | NULL | | 2022-03 | 0.7 | -54 | ALERT | | 2022-04 | 1.26 | 80 | NULL | | 2022-05 | 0.56 | -55 | ALERT | ## Worked solution and explanation ### What this problem really is This is a NULL-handling problem inside a window function, dressed up as a monthly revenue rollup. Anyone can write the monthly CTE and drop in `LAG` to fetch the prior month. The trap is the very first month: it has no predecessor, so `LAG` returns NULL, and that NULL has to flow untouched through the percent change and the flag. Reach for `COALESCE(prev_revenue, 0)` and month one either throws on a zero divide or claims infinite growth, and the whole alert column becomes untrustworthy. There is a quieter second test: the report shows the change in whole points with the fraction dropped, but the alert is judged on the exact change. Flag off the truncated number and a month that fell 10.4% (shown as -10) slips through. --- ### Break down the requirements #### Step 1: Bucket by month and keep the full-precision sum Use `strftime('%Y-%m', transaction_date)` as a sortable month key and `SUM(total_amount)` as the revenue. Keep that sum at full precision inside the CTE: the percent change is computed from it, and the thousands figure is only a presentation of it. Divide the rounded thousands instead and a month that truly grew 78.8% reads 77. #### Step 2: Pull the prior month with `LAG` ordered by month `LAG(revenue) OVER (ORDER BY month)` gives `prev_revenue`. The first month's `prev_revenue` is NULL by design, and that NULL must propagate into `pct_change` and the flag. Do not `COALESCE` it to 0: that turns a missing predecessor into a divide-by-zero. #### Step 3: Name the change once, then truncate and scale only at the edge Compute `pct_change` as `(revenue - prev_revenue) / prev_revenue * 100` in its own CTE so the flag and the displayed value read the same expression. The flag is `CASE WHEN pct_change < -10 THEN 'ALERT' END` with no `ELSE`, tested on the exact value: every other month gets NULL, and because `NULL < -10` is not true, month one stays NULL as well. Only the final `SELECT` applies `CAST(pct_change AS INTEGER)`, which drops the fraction toward zero (-12.7 becomes -12), and `ROUND(revenue / 1000.0, 2)` for thousands. --- ### The solution **Monthly sum, `LAG` for the prior month, truncate and scale at the end** ```sql WITH monthly AS ( SELECT strftime('%Y-%m', transaction_date) AS month, SUM(total_amount) AS revenue FROM transactions GROUP BY strftime('%Y-%m', transaction_date) ), with_prev AS ( SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM monthly ), with_change AS ( SELECT month, revenue, (revenue - prev_revenue) / prev_revenue * 100 AS pct_change FROM with_prev ) SELECT month, ROUND(revenue / 1000.0, 2) AS revenue_thousands, CAST(pct_change AS INTEGER) AS pct_change, CASE WHEN pct_change < -10 THEN 'ALERT' END AS alert FROM with_change ORDER BY month ``` > **One scan, then a few dozen rows** > > At production scale `transactions` is 100M rows, but the monthly rollup collapses it to a few dozen rows, so the `LAG` window, the cast and the rounding are free. The only real cost is the single scan behind `SUM(total_amount)`; partitioning on `transaction_date` or a precomputed daily rollup removes most of it. > **Let the NULL flow, truncate late** > > The first tell is whether you reached for `COALESCE(prev_revenue, 0)` to 'handle' month one; a strong candidate lets the NULL flow and says why `NULL < -10` never flags. The second is where the `CAST` lands: a candidate who truncates first and then tests `pct_change < -10` on the whole number misses every fall between 10% and 11%. > **Display precision is not decision precision** > > Flagging off `CAST(pct_change AS INTEGER) < -10` silently raises the bar to an 11% fall, and swapping the cast for `ROUND(pct_change, 0)` shows -13 where the report wants -12. Decide on the exact value, then truncate only the column you display. --- ## Common follow-up questions - What if you needed year-over-year change instead of month-over-month? _(Swap in `LAG(revenue, 12) OVER (ORDER BY month)`; the structure is identical, only the offset changes, and it assumes no missing months.)_ - How would you handle a month with zero revenue? _(Guard the divisor with `NULLIF(prev_revenue, 0)` so the intent is explicit instead of relying on the engine's divide-by-zero behavior.)_ - How do you backfill a missing calendar month? _(Generate a month calendar and outer-join the rollup to it; otherwise `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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.