Brightlane's data team is auditing product-catalogue completeness and needs each item classified.
Write a query to return each product's name, category_id, and a classification label:
'uncategorized'for products with no category assigned (category_idisNULL).'specialty'for products withcategory_id >= 5.'general'for all other products.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - Some products have a recorded
category_id; some havecategory_idset toNULL(unassigned). - Every product must receive exactly one label — uncategorized products must reach
'uncategorized', not fall through to a numeric comparison.
Output:
- One row per product, with columns
name,category_id, andclassification.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
name,
category_id,
CASE
WHEN category_id IS NULL THEN 'uncategorized'
WHEN category_id >= 5 THEN 'specialty'
ELSE 'general'
END AS classification
FROM
productsThe shape
The IS NULL branch goes first. Uncategorised products are caught immediately, before any comparison touches their category_id. The remaining branches see only rows with real numeric values, so the range tests behave predictably.
Clause by clause
SELECT name, category_idcarries the product name and its raw category ID through to the output. The audit needs both alongside the label.WHEN category_id IS NULL THEN 'uncategorized'is the first branch. It catches every product with no category assigned and returns the explicit label.WHEN category_id >= 5 THEN 'specialty'runs only on rows that survived the first branch — socategory_idis guaranteed to be a real number, and the comparison is safe.ELSE 'general'catches everything else: products withcategory_id < 5.END AS classificationcloses the expression.FROM productsis the source set.
Why this and not the IS NULL branch at the end
Moving WHEN category_id IS NULL THEN 'uncategorized' after the range branches looks like it would still work — a fall-through branch for the leftover case. It doesn't. PostgreSQL's three-valued logic means NULL >= 5 evaluates to NULL, which a WHEN branch treats as not-matching. The same goes for any later range branch. So an uncategorised row would fail category_id >= 5, fail every subsequent comparison, and land in ELSE 'general' if ELSE is the catch-all — or in the IS NULL branch only if it's placed after the ranges but still hit before ELSE. The ordering rule for IS NULL is: place it ahead of any branch whose condition would silently fail on NULL.
The trap
NULL doesn't surface as an error. A range comparison against NULL returns NULL, the branch quietly fails to match, and the row falls through to whatever branch comes next. The audit reports an uncategorised product as 'general', the data team can't tell the difference between "low ID" and "no ID," and the catalogue-completeness signal is gone. Any time NULL is a meaningful category and not just an absence, the IS NULL branch has to come first.
You practiced placing the IS NULL branch ahead of numeric branches in a CASE. The recurring shape any time NULL rows must reach a specific label rather than fall through to ELSE.
