Join Subscriber Data to _Open and _Click Data view

Question

How Can i join _Subscribers to this below query to get email address also?

Select 
s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate,
c.[Click],
o.[Open]
FROM ( 
    SELECT
    sn.SubscriberKey,
    sn.JobID,
    sn.BatchID,
    sn.EventDate
    FROM [_sent] sn
) s
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Open], JobID
    FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Click], JobID
    FROM [_Click] cl
    GROUP BY cl.SubscriberKey, cl.JobID
) c
ON s.SubscriberKey = c.SubscriberKey
WHERE s.JobID = c.JobID
AND s.JobID = o.JobID
AND s.JobID IS NOT NULL

Answer 1

Simply add an INNER JOIN between _Sent and _Subscribers at some point. Assumption: This query runs in the Parent BU.

SELECT 
sn.SubscriberKey,
sub.EmailAddress
FROM _Sent sn
INNER JOIN
_Subscribers sub
on sn.SubscriberID = sub.SubscriberID

Something else: Generally it is recommended to split your query when working with data views. You will see strong declines in performance (and thus: timeouts) once you have several JOINS on data views in one big query and have 6 months of data. I'd hazard a guess and say that your query will start running into timeouts in a large volume setup sooner or later.

When splitting the query, it's pretty much up to you if you start with the INNER JOIN or end with the INNER JOIN.

I'd go in this order:

1) INNER JOIN sent / subscribers

2) LEFT JOIN result of 1 / Open

3) LEFT JOIN result of 2 / Click

hope this helps!