import re
 
qry = ''' 
with 
qry_1 as ( 
   SELECT ID, 
          NAME
   FROM   ( ... other code...
),
qry_2 as ( 
    SELECT coalesce (table1.ID, table2.ID) as ID,
           NAME
   FROM (...other code...
),
qry_3 as (
     SELECT id.WEATHER AS WEATHER_MORN,
            ROW_NUMBER() OVER(PARTITION BY id.SUN
                ORDER BY id.TIME) AS SUN_TIME,
            id.RAIN,
            id.MIST
   FROM (...other code..
)
'''
 
print(re.findall(r"^\S+\sas\s*\(\s*((?:\n.*)*?)\n\s*FROM\b", qry, re.M))

aW1wb3J0IHJlCiAKcXJ5ID0gJycnIAp3aXRoIApxcnlfMSBhcyAoIAogICBTRUxFQ1QgSUQsIAogICAgICAgICAgTkFNRQogICBGUk9NICAgKCAuLi4gb3RoZXIgY29kZS4uLgopLApxcnlfMiBhcyAoIAogICAgU0VMRUNUIGNvYWxlc2NlICh0YWJsZTEuSUQsIHRhYmxlMi5JRCkgYXMgSUQsCiAgICAgICAgICAgTkFNRQogICBGUk9NICguLi5vdGhlciBjb2RlLi4uCiksCnFyeV8zIGFzICgKICAgICBTRUxFQ1QgaWQuV0VBVEhFUiBBUyBXRUFUSEVSX01PUk4sCiAgICAgICAgICAgIFJPV19OVU1CRVIoKSBPVkVSKFBBUlRJVElPTiBCWSBpZC5TVU4KICAgICAgICAgICAgICAgIE9SREVSIEJZIGlkLlRJTUUpIEFTIFNVTl9USU1FLAogICAgICAgICAgICBpZC5SQUlOLAogICAgICAgICAgICBpZC5NSVNUCiAgIEZST00gKC4uLm90aGVyIGNvZGUuLgopCicnJwogCnByaW50KHJlLmZpbmRhbGwociJeXFMrXHNhc1xzKlwoXHMqKCg/OlxuLiopKj8pXG5ccypGUk9NXGIiLCBxcnksIHJlLk0pKQ==