SELECT EmailAddress, SubscriberKey, i.LastModifiedDate
 
FROM (
SELECT
DISTINCT LOWER(r.EmailAddress), c.Id AS SubscriberKey, i.LastModifiedDate,
 
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)
LEFT JOIN [7011G000000OJtMQAW_CI_MR_SIS2GO_rawdata] r ON LOWER(r.EmailAddress) = LOWER(i.Email__c)
LEFT JOIN [7011G000000OJtMQAW_CI_MR_SIS2GO_sendable] s ON LOWER(s.EmailAddress) = LOWER(r.EmailAddress)

WHERE
    Email__c IS NOT NULL
    AND r.EmailAddress IS NOT NULL
    AND s.SubscriberKey IS NULL
   
        ) t2
 
WHERE RowNum = 1