in general end to end tests/whole system tests which mock/stub as little as possible and are focused on testing whole use-case flows is a good starting point (sadly in many teams also a constant uphill battle from convincing PM over allocate time to bootstrap it to making CI harder due to basically having to bootstrap a whole testing system including db etc. to educating devs to write such tests properly, instead of writing unit tests at a end to end test level (which rarely ends well).

Optimal you then combine this with automatic collection and analysis of log traced to detect performance regression (which is hard but just because of the involved tooling but now it needs a CI env with reliable perf. characteristics e.g. not GitHub hosted GitHub action runners).

Through in this case the problem is a trap in the behavior of `IN (...)` sub queries which probably is not specific to WITH clause and might be as deeply rooted as the SQL standard but a you have to pay for being able to read it I'm not sure.