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..
)
'''
 
find_params = [
    re.findall('^\w+|NAME|(?<=\.)NAME', i) for i in
               re.findall(r"^\S+\sas\s*\(\s*((?:\n.*)*?)\n\s*FROM\b", qry, re.MULTILINE | re.IGNORECASE)
]
 
print(find_params)

aW1wb3J0IHJlCgpxcnkgPSAnJycgCndpdGggCnFyeV8xIGFzICggCiAgIFNFTEVDVCBJRCwgCiAgICAgICAgICBOQU1FCiAgIEZST00gICAoIC4uLiBvdGhlciBjb2RlLi4uCiksCnFyeV8yIGFzICggCiAgICBTRUxFQ1QgY29hbGVzY2UgKHRhYmxlMS5JRCwgdGFibGUyLklEKSBhcyBJRCwKICAgICAgICAgICBOQU1FCiAgIEZST00gKC4uLm90aGVyIGNvZGUuLi4KKSwKcXJ5XzMgYXMgKAogICAgIFNFTEVDVCBpZC5XRUFUSEVSIEFTIFdFQVRIRVJfTU9STiwKICAgICAgICAgICAgUk9XX05VTUJFUigpIE9WRVIoUEFSVElUSU9OIEJZIGlkLlNVTgogICAgICAgICAgICAgICAgT1JERVIgQlkgaWQuVElNRSkgQVMgU1VOX1RJTUUsCiAgICAgICAgICAgIGlkLlJBSU4sCiAgICAgICAgICAgIGlkLk1JU1QKICAgRlJPTSAoLi4ub3RoZXIgY29kZS4uCikKJycnCgpmaW5kX3BhcmFtcyA9IFsKICAgIHJlLmZpbmRhbGwoJ15cdyt8TkFNRXwoPzw9XC4pTkFNRScsIGkpIGZvciBpIGluCiAgICAgICAgICAgICAgIHJlLmZpbmRhbGwociJeXFMrXHNhc1xzKlwoXHMqKCg/OlxuLiopKj8pXG5ccypGUk9NXGIiLCBxcnksIHJlLk1VTFRJTElORSB8IHJlLklHTk9SRUNBU0UpCl0KCnByaW50KGZpbmRfcGFyYW1zKQ==