Advanced Querying — Recursive CTEs & Window Function Extras

mysql3-6 already covered window functions in real depth, and this course isn't going to re-teach ROW_NUMBER, RANK, or PARTITION BY from scratch. This chapter covers exactly two things: a feature MySQL only gained much later than Postgres (recursive CTEs), and a set of genuinely Postgres-specific window function extras that go beyond the shared ANSI SQL baseline.

Recursive CTEs

Both engines support ordinary (non-recursive) CTEs via WITH. A recursive CTE, using WITH RECURSIVE, lets a query reference itself, building up a result iteratively — the standard way to traverse a hierarchical structure (an org chart, a category tree, a bill of materials) whose depth isn't known in advance.

A recursive CTE has two parts, unioned together with UNION ALL: an anchor term (the non-recursive base case) and a recursive term (which references the CTE's own name and is executed repeatedly until it returns no more rows).

A Worked Example — The Employee Hierarchy

-- employees table has a self-referencing manager_id
WITH RECURSIVE org_chart AS (
  -- Anchor: the manager we're starting from
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 7  -- the manager whose full team we want

  UNION ALL

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

This returns every employee reporting to manager 7, at any depth — direct reports, their reports, and so on — with no fixed limit on how many levels deep the hierarchy goes.

Window Function Extras

Both engines support the core ANSI SQL window function specification — mysql3-6 already covered that shared ground. This chapter covers what Postgres adds beyond it.

• The FILTER clause — aggregate FILTER (WHERE condition) is a genuinely cleaner way to do conditional aggregation than MySQL's CASE WHEN trick:

-- Postgres
SELECT COUNT(*) FILTER (WHERE status = 'completed') AS completed_count
FROM orders;

-- MySQL's equivalent
SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count
FROM orders;

• Named window definitions — a WINDOW clause lets a window definition be declared once and reused across several function calls in the same query, instead of repeating an identical OVER(...) clause each time:

SELECT
  name,
  RANK() OVER w AS rank,
  AVG(salary) OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

• Ordered-set aggregates — Postgres natively supports statistical aggregates like percentile_cont, percentile_disc, and mode(), which MySQL doesn't provide as built-in functions at all.

Combining Recursive CTEs and Window Functions

The two features compose naturally — the employee hierarchy example above could add a window function to rank each employee's direct reports by salary within their own manager's group, applied directly on top of the recursive result set, since a recursive CTE's output is just an ordinary result set once it's finished expanding.

Hands-On Exercises