Drill 1 of 12 in General questions
0

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…