The Revenue Cliff

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

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

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

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.)