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.
, CAST(REPEAT_CNT AS DECIMAL(18,4)) /CAST(call_cnt AS DECIMAL(18,4)) AS REPEAT_RATE
, 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 *********/
, EXTRACT(YEAR FROM x.date_event) AS year_event
, MAX(CASE WHEN Y.date_event IS NULL THEN 0 ELSE 1 END )AS prior7days
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
GROUP BY 1,2
can anyone help with how i might achieve the deliniation to the group code without the duplicate values?