SELECT
EmailAddress, Region, Industry__c, SubscriberKey, Consent_Level_Summary__c, Mailing_Country__c, Language__c, CreatedDate,
FirstName, LastName, DealerCode, MainDealer, Cat_Campaign_Most_Recent__c, Business_Unit__c
 
FROM (
SELECT
DISTINCT LOWER(Email__c) AS EmailAddress, i.Region__c AS Region, i.Industry__c,
c.Id AS SubscriberKey, c.Consent_Level_Summary__c, i.Mailing_Country__c, c.Language__c, i.CreatedDate,
c.FirstName, c.LastName, r.DealerCode, r.MainDealer, r.Cat_Campaign_Most_Recent__c, r.Business_Unit__c,

ROW_NUMBER() OVER(PARTITION BY c.ID ORDER BY i.LastModifiedDate DESC) as RowNum
 
FROM ent.Interaction__c_Salesforce i
JOIN ent.Contact_Salesforce_1 c ON LOWER(c.Email) = LOWER(i.Email__c)
JOIN [7011G000000OJtMQAW_CI_MR_SIS2GO_Welcome_rawdata] r ON LOWER(r.MAIL) = LOWER(i.Email__c)
LEFT JOIN [7011G000000OJtMQAW_CI_MR_SIS2GO_SIS2GO_Exclude_Dealer_Competitor_Agency] e ON LOWER(r.Mail) = LOWER(e.MAIL)
 
WHERE
    e.Mail IS NULL
    AND Email__c IS NOT NULL
    AND  (i.System_Language__c like 'en_%' OR (i.Mailing_Country__c != 'CA' AND i.System_Language__c is null))
   
        )t2
 
WHERE RowNum = 1