Flag subscription churn risk

Question

A retention squad triages accounts by how long they have been inactive and whether auto-renew is switched on.

From `subscriptions`, return `account_code`, `days_inactive`, `auto_renew` and a `risk_band` computed as:

- `Critical` when `days_inactive` is 60 or more and `auto_renew` is false
- `High`

Accepted Answer

Step 1: List the bands from most specific to least specific — `CASE` short-circuits on the first true branch, so ordering *is* the logic. Step 2: The `Critical` branch needs both conditions: `days_inactive >= 60 AND NOT auto_renew`. Step 3: The `High` branch can then simply test `days_inactive >= 60`, because any row reaching it already has auto-renew on. Step 4: `Watch` becomes `days_inactive >= 30`; the upper bound of 59 is implied by the earlier branch. Step 5: `ELSE 'Healthy'` catches everything under 30 days. Alias the expression as `risk_band`.

Flag subscription churn risk

More CASE Statements questions

Asked at ZoomInfo