not seeing anything wrong , but know that your close to making this work.. I do use CTE's in Databricks SQL. make sure your query works as expected outside the CTE. another work around is make the cte portion into a dataframe then create a view from the dataframe, and use it for joins in your sql statement.