SELECT
AMC_Status__c,Job_Role__c,AMC_Last_Activity_Date__c, Industry_Level_2_Master__c, Industry__c, SubscriberKey, Consent_Level_Summary__c,
Business_Unit__c, Region, Cat_Campaign_Most_Recent__c, Mailing_City__c, Mailing_Country__c, LastModifiedDate, Language__c AS LanguageCode, CreatedDate
 
FROM (
SELECT
DISTINCT i.Region__c AS Region,c.AMC_Status__c,c.Job_Role__c,c.AMC_Last_Activity_Date__c, i.Industry_Level_2_Master__c, i.Industry__c,
c.Id AS SubscriberKey, c.Consent_Level_Summary__c, i.Business_Unit__c,i.Cat_Campaign_Most_Recent__c , i.Mailing_City__c, i.Mailing_Country__c, i.LastModifiedDate, c.Language__c, i.CreatedDate,
 
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 ep_mr_en_us_w170049_Exclusions_Dealers_Agency_Competitor exc ON LOWER(c.Email) = LEFT JOIN [ep_mr_en_us_w170049_Exclusions_Dealers_Agency_Competitor] e ON LOWER(c.Email) = LOWER(e.EmailAddress)

WHERE
e.EmailAddress IS NULL
AND ((
i.Business_Unit__c LIKE '%Electric Power%' OR
i.Business_Unit__c = 'EP' OR
i.Industry__c LIKE '%Electric Power%' OR
i.Industry_Level_2_Master__c in ('Data Centers', 'Emergency Power', 'Power Plants', 'Power Generation')
)
    AND (Email__c IS NOT NULL OR exc.EmailAddress IS NOT NULL)
    AND ((i.Mailing_Country__c = 'US' AND  c.Consent_Level_Summary__c in ('Express Consent' , 'Validated Consent', 'Legacy Consent')) OR (i.Mailing_Country__c != 'US' AND  c.Consent_Level_Summary__c in ('Express Consent' , 'Validated Consent')))
    AND Email__c NOT LIKE '%@cat.com'
    AND i.Mailing_Country__c IS NOT NULL
    AND c.AMC_Status__c = 'Active'
    AND c.AMC_Last_Activity_Record_ID__c <> 'Not Marketable'
    AND  (i.System_Language__c like 'en_%' OR (i.Mailing_Country__c != 'CA' AND i.System_Language__c is null))
   
        ))t2
 
WHERE RowNum = 1