Recursive CTEs & Analytics

Preview — 3 of 10 questions

What keyword is required to write a self-referencing CTE in PostgreSQL?

javascript

WITH RECURSIVE org_chart AS (
  -- Anchor: start from the CEO
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON oc.id = e.manager_id
)
SELECT * FROM org_chart ORDER BY depth, name;

ARECURSIVE

BLOOP

CITERATE

DHIERARCHICAL

How do you accumulate a text path (e.g., /CEO/VP/Manager) in a recursive CTE?

AUse STRING_AGG in the recursive member's SELECT

BUse ARRAY_AGG in the recursive member

CRecursive CTEs cannot accumulate string paths

Dpath || '/' || name in the recursive member using a text column

What makes a LATERAL join different from a regular subquery in the FROM clause?

ALATERAL is equivalent to a CROSS JOIN

BLATERAL allows the subquery to return multiple columns

CLATERAL subqueries can reference columns from tables listed earlier in the same FROM clause

DLATERAL forces the subquery to be materialized

Answer all 10 questions (7 more), see explanations for every answer, and track your score.