> Is cross-joining a CTE to itself realistic?

Yes! Technically you have to say `WITH RECURSIVE ...` first, but yes, it's a very common thing to do to, for example, compute the transitive closure of nested groups. Just remember to use `UNION` not `UNION ALL`! (Otherwise you will get an infinite loop if there's a circular group.)

  WITH RECURSIVE closure AS (
    // Seed the closure
    SELECT parent AS parent, child AS child
    FROM groups
    // Repeat the right side of the UNION
    // until no new rows are added
    UNION
    // Recursively join the closure to the
    // groups
    SELECT g.parent, c.child
    FROM groups g
    JOIN closure c ON g.child = c.parent
  );