OpenClick_salesforce.sql

SELECT 
    j.JobID,
    j.EmailName,
    j.DeliveredTime AS SendTime,
    o.EventDate AS EventTime,
    s.EmailAddress,
    s.SubscriberKey,
    'Opened' AS EventType
FROM [_Job] j
INNER JOIN [_Open] o ON j.JobID = o.JobID
INNER JOIN [_Subscribers] s ON o.SubscriberID = s.SubscriberID
WHERE o.IsUnique = 1
  AND o.EventDate > DATEADD(day, -90, GETDATE())

UNION ALL

SELECT  
    j.JobID,
    j.EmailName,
    j.DeliveredTime AS SendTime,
    c.EventDate AS EventTime,
    s.EmailAddress,
    s.SubscriberKey,
    'Clicked' AS EventType
FROM [_Job] j
INNER JOIN [_Click] c ON j.JobID = c.JobID
INNER JOIN [_Subscribers] s ON c.SubscriberID = s.SubscriberID
WHERE c.IsUnique = 1
  AND c.EventDate > DATEADD(day, -90, GETDATE())