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))

aW1wb3J0IHJlCgpxcnkgPSAnJycgCndpdGggCnFyeV8xIGFzICggCiAgIFNFTEVDVCBJRCwgCiAgICAgICAgICBOQU1FCiAgIEZST00gICAoIC4uLiBvdGhlciBjb2RlLi4uCiksCnFyeV8yIGFzICggCiAgICBTRUxFQ1QgY29hbGVzY2UgKHRhYmxlMS5JRCwgdGFibGUyLklEKSBhcyBJRCwKICAgICAgICAgICBOQU1FCiAgIEZST00gKC4uLm90aGVyIGNvZGUuLi4KKSwKcXJ5XzMgYXMgKAogICAgIFNFTEVDVCBpZC5XRUFUSEVSIEFTIFdFQVRIRVJfTU9STiwKICAgICAgICAgICAgUk9XX05VTUJFUigpIE9WRVIoUEFSVElUSU9OIEJZIGlkLlNVTgogICAgICAgICAgICAgICAgT1JERVIgQlkgaWQuVElNRSkgQVMgU1VOX1RJTUUsCiAgICAgICAgICAgIGlkLlJBSU4sCiAgICAgICAgICAgIGlkLk1JU1QKICAgRlJPTSAoLi4ub3RoZXIgY29kZS4uCikKJycnCgpwcmludChyZS5maW5kYWxsKHIiXlxTK1xzYXNccypcKFxzKigoPzpcbi4qKSo/KVxuXHMqRlJPTVxiIiwgcXJ5LCByZS5NKSk=