SQL-Abfrage mit Sub-Sub-Select ;)

[B.Hauser]

Was die Optimierung angeht, so ist es egal ob CTE's oder Subselect (Derived Table).
CTE's werden in Derived Table umgeschrieben und je Verwendung (also auch durchaus mehrfach) ausgeführt.

Bekannt ist, dass die SQL-Abfragen nach bestimmten Algorithmen umgeschrieben werden, aber wie das umgeschriebene SQL-Statement dann aussieht, weißt weder Du noch ich.

Allerdings zeigt die Erfahrung, dass bei komplexen Abfragen mit mehrfach verschachtelten Sub-Selects an allen möglichen Stellen (SELECT-Liste, FROM-Anweisung, WHERE-Bedingungen, ORDER BY) dass sowohl der Programmierer des öfteren den Überblick verliert, als auch der Optimizer irgendwo ins trudeln kommt, so dass ggf. gleiche (Sub-)Selects mehrfach ausgeführt werden.
CTEs hingegen werden am Anfang eines SELECT-Statements angegeben und können einzeln ausgeführt werden und sind deshalb (auch von dem Optimizer) wesentlich einfacher zu analysieren.

LATERAL braucht man eigentlich nur dann, wenn Spalten-Werte aus der ersten Tabelle/View als Parameter an User Defined Functions (UDTF) übergeben werden müssen.
Alles andere kann mit "gewöhnlichen" JOINS (incl. CROSS JOIN) gelöst werden. Das zuvor angeführte Beistpiel bringt auch ohne LATERAL die gleiche Lösung:

from tablea tbla 
     left join (select a, b, c, aggr(x) AggX, aggr(y)
                  from tableb b
                  group by a b c
               ) tblb 
     on     a = tbla.Key1 
        and b = tbla.Key2 
        and c = tbla.Key3

Allerdings hat das alles wiederum nichtsmit der ursprünglichen Frage bzw. Problemlösung zu tun.