e. fn_determine_60_day_message_count_emv():

DROP FUNCTION fn_determine_60_day_message_count_emv(in_target_profileble_id integer, in_service_name text);
CREATE FUNCTION fn_determine_60_day_message_count_emv(in_target_profileble_id integer, in_service_name text) RETURNS integer
    LANGUAGE plpgsql
    AS $$

DECLARE
      
      var_message_count_emv int := null;
     
BEGIN 
    -- find out how many messages were to the receiver during this time period
    with cte as
    (       
        select count_60_day
          from contacts  
          join (
            select batch_id from activities
            where activities.service_name = in_service_name
          ) as t on t.batch_id = contacts.batch_id  
         where contacts.base_social_profileable_id = in_target_profileble_id
    )
    SELECT MAX(count_60_day) 
      INTO var_message_count_emv
      FROM cte;    
      
    if(var_message_count_emv is null) then
        var_message_count_emv := 0;
    end if;
    
    return  var_message_count_emv;           
         
END; 
$$;