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?

javascript

WITH RECURSIVE org_chart AS (
  SELECT id, name, '/' || name AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, oc.path || '/' || e.name
  FROM employees e JOIN org_chart oc ON oc.id = e.manager_id
)
SELECT * FROM org_chart;

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?

javascript

SELECT u.name, recent.title, recent.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT title, created_at
  FROM posts
  WHERE posts.user_id = u.id  -- references outer table u
  ORDER BY created_at DESC
  LIMIT 3
) recent ON true;

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.