Streamhub's analytics team needs to tag users by paid-plan tier. The mapping for paid plans is:
starter→'entry'pro→'growth'enterprise→'enterprise'
Free-plan users should appear in the result with no tag — their tag is missing.
Write a query to return every user's ID, plan, and tag.
Assumptions:
- The
userstable contains every account on the Streamhub platform. - The
plancolumn has four values:free,starter,pro, andenterprise. - Every user appears in the output, including free-plan users — their tag is simply missing.
Output:
- One row per user, with columns
id,plan, andtag. Rows for free-plan users will have a missingtag.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
plan,
CASE
WHEN plan = 'starter' THEN 'entry'
WHEN plan = 'pro' THEN 'growth'
WHEN plan = 'enterprise' THEN 'enterprise'
END AS tag
FROM
usersThe shape
Three WHEN branches cover the three paid plans, and there's no ELSE. When a free-plan user falls through every branch, CASE returns NULL by default — which is exactly what the prompt asks for: a missing tag on free-plan rows.
Clause by clause
SELECT id, planreturns the user's ID and raw plan value, so free-plan rows are visible in the result alongside their missing tag.WHEN plan = 'starter' THEN 'entry',WHEN plan = 'pro' THEN 'growth', andWHEN plan = 'enterprise' THEN 'enterprise'are the three explicit branches. Each is an equality test against a specific paid-plan value; since the plan values are mutually exclusive, branch order doesn't matter.- There is no
ELSE. For a free-plan user, none of the threeWHENconditions evaluate to true. PostgreSQL's defined behavior is to returnNULLwhen no branch matches and noELSEis present. END AS tagcloses the expression and labels the column. Free-plan rows have this column populated withNULL; paid-plan rows have the mapped string.FROM usersis the source set: every account on the platform, including free users.
Why this and not ELSE NULL
Writing the same query as ... ELSE NULL END AS tag returns the identical result — same rows, same values, same NULLs on free-plan users. The two forms are functionally interchangeable. The trade-off is readability: an explicit ELSE NULL makes the intent visible in the code ("free-plan users get nothing, on purpose"), while the omitted-ELSE form relies on the reader knowing the default behavior. For production work, the explicit form is the better habit.
The canonical query here drops the ELSE to demonstrate the default. Either form is correct; the explicit form is just more honest about what's happening.
The trap
The trap is reading an omitted ELSE as ELSE 0 or ELSE '' rather than ELSE NULL. A missing branch never returns a placeholder value — it returns the absence of a value. That distinction matters anywhere the CASE result feeds into something type-sensitive: an empty string and a NULL are different inputs, a zero and a NULL produce different downstream sums, and NULL = NULL is NULL, not true. The omitted ELSE is the cleanest way to say "no value applies," but it's also the easiest to misread as a defaulting behavior it doesn't have.
You practiced relying on CASE's default behavior when no WHEN branch matches and no ELSE is present. Writing an explicit ELSE NULL makes the intent visible.
