WITH (CTE)

WITH clause allows creating common table expressions (CTE), inline named views, that can be referenced in the main query potentially multiple times.

Say we want to compare the average body mass of the penguins by (island, species) and (island, sex).

note

The above is only to serve as an example for the WITH clause. There are better ways to write the same SQL Frame using analytics functions. See Top N and Others for real examples of using WITH clause.

WITH RECURSIVE

CTEs can also be recursive as shown in the example below. See art in a DataFrame for a fun example of WITH RECURSIVE clause that is much more complex.

WITH (CTE)

WITH RECURSIVE​

WITH RECURSIVE