Self Joins & Hierarchical Queries SQL Topic exerciseHardVerified answerSQLite + PostgreSQL live · 3 guided

DFS Pre-Order Org Chart with Indentation

Print the org chart in depth-first pre-order: parents appear before their children, sibling order = ascending employee_id. Each employee_name is prefixed with two spaces per level above 1. Carry a sort_path string built from zero-padded employee_ids so ORDER BY sort_path produces correct DFS order. Return employee_id, indented_name, hierarchy_level, sort_path — ordered by sort_path.

  • Recursive CTE
  • Joins
  • Subqueries
  • String functions
  • NULL handling

Exercise brief

Understand the request

Org experience designer An org-chart renderer needs deterministic depth-first pre-order with parents before their children.

Return

  • Return employee_id, indented name, level, and sort path.
  • Order by the generated sort path.

Constraints

  • Carry a zero-padded stable-ID path.
  • Indent by two spaces per level below the root.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • employee_nameTEXT
  • manager_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Build a sort_path column inside the recursion: zero-pad each id and concatenate with a separator.

Hint 2

Lexicographic sort on the sort_path produces correct DFS pre-order traversal.

Hint 3

Indentation = repeated spaces, two per level above 1.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH RECURSIVE org AS (SELECT employee_id, employee_name, manager_id, 1 AS hierarchy_level, printf('%05d', employee_id) AS sort_path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, o.hierarchy_level + 1, o.sort_path || '/' || printf('%05d', e.employee_id) FROM employees e INNER JOIN org o ON e.manager_id = o.employee_id) SELECT employee_id, substr('                                ', 1, (hierarchy_level - 1) * 2) || employee_name AS indented_name, hierarchy_level, sort_path FROM org ORDER BY sort_path;

Why this works

DFS pre-order is the natural way humans read an org chart: each parent immediately above its children, indented by depth. The sort_path trick (zero-padded ids joined by a separator) avoids recursive ORDER BY pitfalls.

Success check

The full org chart renders in stable parent-before-child depth-first order.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idindented_namehierarchy_levelsort_path
1Alice CEO100001
2 Bob VP Sales200001/00002
5 Emma Sales Mgr300001/00002/00005
8 Henry Sales Rep400001/00002/00005/00008
9 Ivy Sales Rep400001/00002/00005/00009
13 Mia Overpaid Jr400001/00002/00005/00013
15 Olivia Sales Rep400001/00002/00005/00015
3 Carol VP Eng200001/00003
6 Frank Eng Mgr300001/00003/00006
10 Jack Engineer400001/00003/00006/00010

Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.