We find that if we ever join to a CTE from another CTE and in the final query, it now fails in sandbox but still works in production. Is anyone else having this issue?

This is an issue in NetSuite 2024.1. Here's the defect. You can contact support and ask them to add you to it, so you can be notified of its status updates.

765188 : SuiteScripts > SuiteQL > Common Table Expressions (CTEs) > Unexpected Errors

Got it. Thanks!