Here's how to rewrite the query in a concise and easy-to-understand way in MS SQL:

WITH CTE AS (
    SELECT 
    ...