Query to gather records for External Email Campaign Events ETL

MCP_ExternalEmailCampaignEventsETL

/* ************************************
- Salesforce Marketing Cloud Personalization (formerly Interaction Studio, formerly Evergage)
- External Email Campaign Events ETL
- https://help.salesforce.com/s/articleView?id=sf.mc_pers_etl_external_email_campaign_events_data_feed.htm&type=5
************************************ */

SELECT
    sub.emailAddress AS [attribute:emailAddress],
    s.SubscriberID AS [attribute:sfmcSubscriberID],
    j.EmailID AS [externalCampaignId],
    j.EmailName AS [externalCampaignName],
    'Send' AS [eventType],
    CONVERT(datetimeoffset(33), s.eventDate, 126) AS [eventDate],
    'Salesforce Marketing Cloud Engagement' AS [externalSystem]
FROM 
    _Sent s
INNER JOIN 
    _Job j ON s.JobID = j.JobID
INNER JOIN 
    ENT._Subscribers sub ON s.SubscriberID = sub.SubscriberID
WHERE 
    s.eventDate >= DATEADD(hour, -24, GETDATE())

UNION ALL

SELECT
    sub.emailAddress AS [attribute:emailAddress],
    o.SubscriberID AS [attribute:sfmcSubscriberID],
    j.EmailID AS [externalCampaignId],
    j.EmailName AS [externalCampaignName],
    'Open' AS [eventType],
    CONVERT(datetimeoffset(33), o.eventDate, 126) AS [eventDate],
    'Salesforce Marketing Cloud Engagement' AS [externalSystem]
FROM 
    _Open o
INNER JOIN 
    _Job j ON o.JobID = j.JobID
INNER JOIN 
    ENT._Subscribers sub ON o.SubscriberID = sub.SubscriberID
WHERE 
    o.eventDate >= DATEADD(hour, -24, GETDATE())

UNION ALL

SELECT
    sub.emailAddress AS [attribute:emailAddress],
    c.SubscriberID AS [attribute:sfmcSubscriberID],
    j.EmailID AS [externalCampaignId],
    j.EmailName AS [externalCampaignName],
    'Click' AS [eventType],
    CONVERT(datetimeoffset(33), c.eventDate, 126) AS [eventDate],
    'Salesforce Marketing Cloud Engagement' AS [externalSystem]
FROM 
    _Click c
INNER JOIN 
    _Job j ON c.JobID = j.JobID
INNER JOIN 
    ENT._Subscribers sub ON c.SubscriberID = sub.SubscriberID
WHERE 
    c.eventDate >= DATEADD(hour, -24, GETDATE())