How to write one better sql of below two statements to improve the performance

Question

If more than one ID for a match select id with max(date) . if more than one id for above max dates select max(ID) Go to TABLE1 to get high-dated IDS

Step 1 : identifying IDs which are having max dates

CREATE MULTISET VOLATILE TABLE TEST_VT 
AS 
(                                       
SELECT
TABLE1.ID ,
TABLE1.KEY1,
TABLE1.STRT_DT,
TABLE1.TERM_DT,
Rank() OVER (PARTITION BY  TABLE1.KEY1 ORDER BY TABLE1.STRT_DT DESC , TABLE1.ID DESC) RNK
FROM TABLE2          
INNER JOIN TABLE1         
ON TABLE2.KEY1=TABLE1.KEY1    
WHERE  TABLE1.TERM_DT='8888-12-31'
QUALIFY RNK=1
GROUP BY 
1,2,3,4
)
WITH DATA PRIMARY INDEX(ID,KEY1) ON COMMIT PRESERVE ROWS;

Go to TABLE1 to get high-dated KEY1S associated with IDS from ABOVE STEP Step 2 : If there is more than one max date take max ID out of all matched records in above step

SELECT                                      
TABLE1.ID ,
TABLE1.KEY1,
TABLE1.STRT_DT,
TABLE1.TERM_DT,                             
RANK() OVER (PARTITION BY TABLE1.KEY1 ORDER BY TABLE1.STRT_DT DESC , TABLE1.ID DESC) AS RNK                 
FROM  TABLE1    
INNER JOIN TEST_VT                                      
ON TEST_VT.ID=TABLE1.ID 
INNER JOIN TABLE3                   
ON TABLE3.KEY1=TABLE1.KEY1    
WHERE  TABLE1.TERM_DT='8888-12-31'  
QUALIFY RNK=1 
GROUP BY    
1,2,3,4