Python comprehensions, compiled to SQL.

Say it once, in Python

For each school with at least one program: the average, over its departments, of the number of courses worth more than 3 credits.

School.query(
  lambda x: x.get_foreign('Program')
             .count() > 0
).fmap(
  lambda x:
    x.get_foreign('Department').fmap(
      lambda y:
        y.get_foreign('Course')
         .filter(lambda z: z.credits > 3)
         .count()
    ).avg() % x.name
)

SELECT q2.avg_n, s.name
FROM School AS s
LEFT JOIN (
  SELECT AVG(COALESCE(q1.n, 0)) AS avg_n,
         d.school_code
  FROM Department AS d
  LEFT JOIN (
    SELECT COUNT(c.course_code) AS n,
           c.dept_code
    FROM Course AS c
    WHERE c.credits > 3
    GROUP BY c.dept_code
  ) AS q1 ON q1.dept_code = d.dept_code
  GROUP BY d.school_code
) AS q2 ON q2.school_code = s.school_code
WHERE EXISTS (
  SELECT 1 FROM Program AS p
  WHERE p.school_code = s.school_code)

An inner lambda can reference an outer lambda’s row at any depth. The compiler handles it by dependent-join unnesting: the subquery gets a private copy of the outer table as its correlation domain, and successive passes pull the join conditions up until everything is flat. Aggregate subqueries are marked LEFT JOINso an empty group still produces a row — that’s where the Python semantics live.

Passes, to a fixpoint

1. Decorrelate. internalize_correlated_tables gives each inner scope its own copy of the outer tables it closes over; join conditions are pulled upward. Iterated to a fixpoint, because pulling one join condition up can expose the next.
2. Flatten. Non-aggregate subqueries are merged into their parent. Aggregates are refused — flattening one would silently drop empty-group rows.
3. Optimize, cost-only. count() > 0 rewrites to EXISTS; a COALESCEis elided when the surrounding predicate already rejects NULL the same way it rejects zero — which keeps the LEFT JOIN eligible for the engine’s own join simplification. Every rewrite is result-preserving, and the test suite is the proof.

One hard-won invariant, straight from a comment in compile.py: the passes track shared subqueries by object identity, so the pipeline must run in place on a single private deep copy — copy mid-pipeline and shared subqueries diverge into inconsistent twins with dangling aliases.

Tested against two ground truths

A quarter of the codebase is the adversarial suite. Every compiled query is executed in DuckDB and diffed against a plain Python comprehension over the same in-memory data.

The fixture data is built to hurt: a school with zero departments, a department with zero courses — the rows that vanish when correlated SQL is written by hand. One suite crosses every comparison operator with thresholds around the NULL/0 boundary, including reflected forms like 0 < count(...). Another nests aggregates three deep with filters at every level.

The harness caught two genuine compiler bugs, both documented in the suite rather than quietly fixed:

1. Unparenthesized OR. filter(A | B).filter(C) emitted x OR y AND z — which SQL parses as x OR (y AND z), silently changing which rows match. Survived every structural test; fell to the adversarial suite.
2. Unscoped cross-scope joins. A reference to a non-key outer column joined against an unconstrained copy of the outer table — large, quiet over-counting. Pinned down with xfail regression tests that record the exact wrong numbers.

Open questions