You have a table folders(id, name, parent_id) representing nested folders. Write a recursive CTE query to list all folders with their full path from the root, separated by '/'. Which query correctly achieves this?
AWITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NOT NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;
BWITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, '/' || name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, '/' || fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;
CWITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;
DWITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path + '/' + f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;
Step-by-Step Solution
Solution:
Step 1: Identify correct base case and path initialization
The base case must select root folders (parent_id IS NULL) and set path to just the folder name without leading '/'. The recursive part builds path as parent_path || '/' || name.
Step 2: Compare options with correct logic
The correct query uses parent_id IS NULL for base, name AS path, and fp.path || '/' || f.name for recursion (PostgreSQL string concat). Distractors fail by using invalid + concat, adding extra '/' (causing '//'), or starting from non-roots (parent_id IS NOT NULL).
Final Answer:
WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path; -> Option C
Quick Check:
Use base root rows + '||' concat for paths [OK]
Quick Trick:Use '||' for string concat and start from root folders [OK]
Common Mistakes:
Using '+' instead of '||' for string concatenation
Starting recursion from non-root folders
Adding extra '/' at path start
Master "Common Table Expressions" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
