Hi SPS,
try below sql, and let me know result
SELECT
Q.CNTCT_ID
,Q.INSTNC_ID
,Q.CMPGN_RUN_DT
,Q.CMPGN_RUN_TM
,Q.CMPGN_RUN_DATE
,Q.TRKING_ID
,Q. USER_ID
,Q.ITEM_ID
,Q.CONV_TYPE_ID
,Q. ACTIVITY_DATE
,Q.AUCT_END_DT
,Q.CSTMZD_LINK_TRKING_DURTN
,LSTG.LEAF_CATEG_ID
,Q.SITE_ID
,Q.TRTMNT_TYPE_CODE
FROM
(
SELECT
CC.CNTCT_ID CNTCT_ID
,CC.INSTNC_ID INSTNC_ID
,CC.CMPGN_RUN_DT CMPGN_RUN_DT
,CC.CMPGN_RUN_TM CMPGN_RUN_TM
,CC.CMPGN_RUN_DATE CMPGN_RUN_DATE
,CC.CMPGN_SENT_DATE
,CC.TRKING_END_DATE
,CC.TRKING_ID TRKING_ID
,CC.USER_ID USER_ID
,WTCH.ITEM_ID ITEM_ID
,3 CONV_TYPE_ID
,WTCH.SRC_CRE_DATE ACTIVITY_DATE
,WTCH.AUCT_END_DT AUCT_END_DT
,CC.CSTMZD_LINK_TRKING_DURTN
CSTMZD_LINK_TRKING_DURTN
,WTCH.SITE_ID SITE_ID
,CC.TRTMNT_TYPE_CODE TRTMNT_TYPE_CODE
FROM SUMM_W CC
JOIN WTCH_TRK WTCH
ON CC.USER_ID = WTCH.WTCHR_ID
QUALIFY WTCH.SRC_CRE_DATE = MIN(WTCH.SRC_CRE_DATE)
OVER(PARTITION BY WTCH.ITEM_ID,WTCH.WTCHR_ID)
WHERE WTCH.SRC_CRE_DATE BETWEEN CC.CMPGN_SENT_DATE AND
CC.TRKING_END_DATE
) Q
,LSTG_ITEM LSTG
,MODEL_SA_MAP CFG
WHERE CFG.AUCT_TYPE_CODE = LSTG.AUCT_TYPE_CODE
AND CFG.INCLD_YN_ID = 1
AND CFG.MODEL_SA_CODE = 'CMC'
AND LSTG.AUCT_END_DT >= '${min_cmpgn_sent_dt}'
AND LSTG.ITEM_ID = Q.ITEM_ID
AND LSTG.AUCT_END_DT = Q.AUCT_END_DT ;
FYI: for sql performance tuning lots of issure needed to take care
table size, Indexes(primary and Secondary), precedence of Residual conditions in Joins etc... hence try the above query.
I have changed just the order or joining the tables, if you can filter the more and more rows initially then joining will take less space and CPU time both.
take care....
Regards,
