Having documented practices and/or workarounds to use in SQL query recipes around CTE is on our todo list, but is not currently available.
It's useful to remember what a SQL query recipe does:
When you validate the query, DSS sends it to the database and asks for the metadata of the result in order to fill the schema of the output dataset based on what the query outputs
When you run the recipe, DSS first creates the output table
Then, DSS rewrites your query to transform the SELECT into a "INSERT FROM SELECT" in the previously-created table. This is where the situation is a bit muddy with CTEs, because most databases don't support CTEs in subqueries.
Then DSS executes the rewritten query
WIth a SQL script recipe, DSS just splits your script in statements and executes them. Hence, you must create the output table, because DSS does not do it for you. The idea is that "forcing you" to do it also allows you to do it in a custom fashion, for example using a CTAS.
However, even if DSS does not create the table, it still knows what table you're supposed to create (this info is in the output dataset). Thus, *after* running your code, DSS can submit a "SELECT *" statement about this table, and inspect the metadata in order to fill the schema of the output dataset.