This query uses a common table expression (WITH statement). When you use Custom SQL in Tableau, it gets wrapped in a SELECT *. Databases generally don't allow common table expressions within a SELECT statement.
It looks like you could rewrite this to not use a common table expression.
I will have to try, I didn't write it to begin with so I am trying not to mess with it too much.
So I went through and did a in depth dive on this query and the tables associated; the query itself actually makes no real sense to me and what it is doing based on the raw data. I have asked the team that built it for clarification on what it is trying to do. Thanks for letting me know that tableau doesn't just run the SQL I give it when I give it custom SQL. Why tableau tries to change my query is beyond me, but I will remember that in the future.