[#49385379] use CTEs to make sure subqueries are not executed multiple times