Aftermarket_Reporting_Initial_Email - Initial (Step 2)

SELECT
 mstr.EmailType, mstr.LanguageCode, mstr.TriggeredSendCustomerKey

    , Count(s.EventDate)  AS Sent
    , Count(o.EventDate)  AS Opens
    , Count(c.EventDate)  AS Clicks
    , Count(b.EventDate)  AS Bounces
    , Count(u.EventDate)  AS Unsubscribes
    
FROM _Sent s
    LEFT JOIN Aftermarket_Reporting_Master mstr ON mstr.subscriberkey = s.subscriberkey
    LEFT JOIN _Job AS job
        ON job.JobID = s.JobID
        
    LEFT JOIN _Open AS o
        ON o.JobID = s.JobID
        AND o.ListID = s.ListID
        AND o.BatchID = s.BatchID
        AND o.SubscriberKey = s.SubscriberKey
        AND o.IsUnique = 1
    LEFT JOIN _Click AS c
        ON s.JobID = c.JobID
        AND c.ListID = s.ListID
        AND c.BatchID = s.BatchID
        AND c.SubscriberKey = s.SubscriberKey
        AND c.IsUnique = 1
    LEFT JOIN _Bounce AS b
        ON s.JobID = b.JobID
        AND b.ListID = s.ListID
        AND b.BatchID = s.BatchID
        AND b.SubscriberKey = s.SubscriberKey
        AND b.IsUnique = 1
    LEFT JOIN _Unsubscribe AS u
        ON s.JobID = u.JobID
        AND u.ListID = s.ListID
        AND u.BatchID = s.BatchID
        AND u.SubscriberKey = s.SubscriberKey
        AND u.IsUnique = 1

        WHERE 
        s.TriggeredSendCustomerKey in ('142092')
        AND DATEDIFF(m,getdate(),s.eventdate) = -1
GROUP BY mstr.EmailType, mstr.LanguageCode, mstr.TriggeredSendCustomerKey