0
SELECT SBSCRPN_KEY, DAILY_DT_KEY, BAL_EXPRY_DT_KEY,  
       RANK() OVER(PARTITION BY SBSCRPN_KEY ORDER BY DAILY_DT_KEY DESC) AS ROW_NUM 
FROM DT_SDMVW.FCT_SBSCRPN_BAL_DAILY 
WHERE DAILY_DT_KEY <= '2017-03-29' 
QUALIFY ROW_NUM <=2

This query takes about more than 40 minutes to complete. Removing RANK and Qualify helps the query execute faster. But i can not remove it as it is a requirment. Any idea on how to optimize it on a query level

Thanks for your help!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Wolf Man
  • 1
  • 2
    Is `FCT_SBSCRPN_BAL_DAILY` a table or a view? How many rows in table/result? Is the table partitioned? Can you show Explain? – dnoeth Mar 30 '17 at 13:40
  • Could you provide English of what your query is trying to achieve. Also, how is the table clustered? – access_granted Apr 19 '17 at 21:16

0 Answers0