Relabel Free Plan as Unclassified — COALESCE and NULLIF in SQL

The problem

Streamhub's growth team is standardizing plan display labels for the admin console. The internal plan name 'free' should not appear in the user interface.

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

Assumptions:

The users table has one row per user with a name and a plan.
Users on the free plan have plan = 'free'.
A user with plan = 'free' should appear with 'unclassified' as their display plan; all other users should show their recorded plan.

Output:

One row per user, with columns name and display_plan.

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,
  COALESCE(NULLIF(plan, 'free'), 'unclassified') AS display_plan
FROM
  users

The shape

COALESCE(NULLIF(plan, 'free'), 'unclassified') runs a two-step rewrite in one expression: the inner NULLIF turns the sentinel 'free' into NULL, then the outer COALESCE turns that NULL into the display label 'unclassified'. Every other plan name passes through both functions unchanged.

Clause by clause

SELECT name, COALESCE(NULLIF(plan, 'free'), 'unclassified') AS display_plan returns each user's name and their display plan label. The expression nests from the inside out: NULLIF checks if plan equals 'free' and rewrites it to NULL when it does; COALESCE then receives that potentially-NULL value and substitutes 'unclassified' when it sees NULL. For a user on 'pro', NULLIF returns 'pro' untouched and COALESCE returns 'pro' untouched.
FROM users reads every user. Free-plan users stay in the result; they are the reason the round-trip exists.

Why this and not a plain `COALESCE(plan, 'unclassified')`

The plain COALESCE only fires when its first argument is NULL. Here, 'free' is a recorded string, not NULL, so a plain COALESCE would leave it in place and the admin console would still display 'free'. The work is to first convert the sentinel into NULL, which is exactly what NULLIF exists for, and only then run the NULL substitution. The two functions are directional opposites that compose cleanly when a stored sentinel needs to become a display value.

You practiced COALESCE(NULLIF(...)) — rewrite a sentinel to missing in the inner step, then rewrite that missing to a display value in the outer step, in one expression.

Return each user's name and their display plan label

The shape

Clause by clause

Why this and not a plain `COALESCE(plan, 'unclassified')`

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

The shape

Clause by clause

Why this and not a plain COALESCE(plan, 'unclassified')

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

Why this and not a plain `COALESCE(plan, 'unclassified')`