I am trying to identify a repeat caller as deliniated by group code I have a data set as that has a colum of event id's (that can potentialy be duplicates) a colum of interaction id's that are unique, a date time field. and a subscription ID
I have been able to identify the the calls at an agragate level but am having dificulty with the deliniation. currently the query looks as follows.
SELECT MONTH_EVENT , Call_Cntr_Call_Grp_Cd , call_cnt , REPEAT_CNT , CAST(REPEAT_CNT AS DECIMAL(18,4)) /CAST(call_cnt AS DECIMAL(18,4)) AS REPEAT_RATE FROM
( SELECT month_event , Call_Cntr_Call_Grp_Cd , Event_Start_DtTm , COUNT( DISTINCT call_cntr_cntct_event_id) AS call_cnt , COUNT( DISTINCT CASE WHEN prior7days = 1 THEN call_cntr_cntct_event_id END) AS REPEAT_CNT
/************************** flag repeat call customer in the past 7 rolling days *********/
( SELECT x.subs_id , EXTRACT(YEAR FROM x.date_event) AS year_event , X.month_event , X.Call_Cntr_Call_Grp_Cd , X.Event_Start_DtTm , X.call_cntr_cntct_event_id , MAX(CASE WHEN Y.date_event IS NULL THEN 0 ELSE 1 END )AS prior7days
FROM TEMP_DB.JSILV_REPEAT X LEFT JOIN TEMP_DB.JSILV_REPEAT Y ON X.subs_id = Y.subs_id AND y.date_event BETWEEN x.date_event-6 AND x.date_event AND x.call_cntr_cntct_event_id <> y.call_cntr_cntct_event_id AND y.event_start_dttm < x.event_start_dttm AND y.Call_Cntr_Call_Grp_Cd = x.Call_Cntr_Call_Grp_Cd
GROUP BY 1,2,3,4,5,6 ) x
GROUP BY 1,2
can anyone help with how i might achieve the deliniation to the group code without the duplicate values?