Advanced SQL

Preview — 3 of 10 questions

What does this recursive CTE return?

javascript

WITH RECURSIVE org_tree AS (
  -- Base case: top-level employees (no manager)
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees reporting to someone already in the tree
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

AAll employees at level 0 only (top of hierarchy)

BThe full organizational hierarchy with depth level, starting from top-level employees and traversing all reports recursively

CAn error — SQL doesn't support recursion

DAll employees sorted by name only

What does LATERAL enable in this query?

javascript

SELECT u.name, recent.order_date, recent.total
FROM users u
JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE orders.user_id = u.id    -- references outer query's u.id
  ORDER BY order_date DESC
  LIMIT 3
) recent ON true;

AA LATERAL join is just a different syntax for INNER JOIN

BLATERAL is required when using LIMIT inside a subquery

CLATERAL makes the subquery run in parallel for performance

DLATERAL allows the subquery to reference columns from tables to its left in the FROM clause, enabling correlated subqueries in the FROM position

What is table partitioning and when should you use it?

javascript

CREATE TABLE logs (
  id BIGINT,
  event TEXT,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE logs_2025 PARTITION OF logs
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

APartitioning divides a large table into smaller physical segments while presenting it as a single logical table — queries on specific partition keys only scan relevant partitions

BPartitioning splits a table across multiple servers for distributed queries

CPartitioning is only useful for tables with more than 1 billion rows

DPartitioning automatically creates indexes on the partition key

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