Interview Question Bank · CASE · EXISTS · NOT IN and NULL
1. Root, inner or leaf
EasyGeneralQuestion #193
Concept: CASE · EXISTS · NOT IN and NULLshow
In an id/parent_id tree, a node with no parent is a root, a node that is someone's parent is inner, and everything else is a leaf. Check the root case first in the CASE, then test for children with EXISTS. Avoid id NOT IN (SELECT parent_id ...): the roots put a NULL in that list, and NOT IN against a list containing NULL is never true.
Example (different data):
SELECT f.id, f.name, EXISTS (SELECT 1 FROM folders sub WHERE sub.parent_folder = f.id) AS has_subfolders FROM folders f;
Your task
Pinecrest Supply, a hardware retailer, is rebuilding its website menu and needs to know which product categories are top-level, which only group other categories, and which hold products directly.
Return id, name and node_type for every category: 'root' if it has no parent, 'inner' if it has a parent and at least one child, otherwise 'leaf'. A category with no parent is a root even if nothing sits under it. Sort by id.
Starting Postgres in your browser…
Checking your access…