Plan Status with Inactive and Trial — COALESCE and NULLIF in SQL

The problem

Streamhub's admin team is building a plan status label for the user dashboard. The label varies by activity status and plan, with one display label reserved for inactive accounts.

Write a query to return each user's name and their plan status label.

Assumptions:

An inactive user (where is_active is FALSE) should always appear with the label 'Inactive' regardless of their plan.
Among active users, those with plan = 'free' should appear with the label 'Trial'.
All other active users should show their recorded plan.

Output:

One row per user, with columns name and plan_status.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  name,
  CASE
    WHEN is_active = FALSE THEN 'Inactive'
    ELSE COALESCE(NULLIF(plan, 'free'), 'Trial')
  END AS plan_status
FROM
  users

The shape

The outer CASE splits users into two populations: inactive accounts get the label 'Inactive' outright, and active accounts run through the nested COALESCE(NULLIF(plan, 'free'), 'Trial') to derive their label from plan. Two independent decision dimensions, activity and plan, meet on the same output column without colliding, because each is handled in its own layer.

Clause by clause

SELECT name, CASE WHEN is_active = FALSE THEN 'Inactive' ELSE COALESCE(NULLIF(plan, 'free'), 'Trial') END AS plan_status returns each user's name and their plan status label. The CASE evaluates the activity test first: inactive users short-circuit straight to 'Inactive' and never touch the plan logic. Active users fall into the ELSE branch, where NULLIF(plan, 'free') rewrites 'free' to NULL and COALESCE(..., 'Trial') then rewrites that NULL to 'Trial'. Active users on any other plan have their plan returned unchanged.
FROM users reads every user. Both inactive accounts and free-plan active accounts stay in the result; they are exactly the rows the two label rewrites are for.

Why a `CASE` wrapping the nested expression and not a longer `COALESCE` chain

COALESCE and NULLIF only see the value of plan. They have no visibility into is_active, so the 'Inactive' label cannot be derived inside the nested expression at all. The inactive-versus-active split is a different dimension than the free-versus-paid split, and the only construct in scope that branches on an arbitrary boolean is CASE. CASE runs first, separates the two populations, and hands only the active rows to the nested label logic.

The trap

Reading the expression top to bottom hides that the CASE runs first and the nested rewrite runs only on the ELSE branch. A reader expecting the COALESCE to also fire on inactive rows would write the wrong label for any inactive user whose plan happens to be 'free'. The execution order is outside-in: the outer construct evaluates first, picks a branch, and only the chosen branch's expression runs at all.

You practiced nesting COALESCE(NULLIF(...)) inside a CASE — an outer category split sets one branch's label outright, while the other branch runs the nested sentinel rewrite.

Return each user's name and their plan status label

The shape

Clause by clause

Why a `CASE` wrapping the nested expression and not a longer `COALESCE` chain

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why a CASE wrapping the nested expression and not a longer COALESCE chain

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why a `CASE` wrapping the nested expression and not a longer `COALESCE` chain