Query to find unique openers in last 30 days

Question

I am trying to find out users who opened an email in the last 30 days from users who received one.
Can someone help me verify if this is the right way to do it?

I found few query examples and few I've used in the past but most of them were not working due to query time out, So I thought to split them into two.

First, I am trying to find out the users who received an email

SELECT 
  s.SubscriberKey,
  s.EventDate AS Sent_Date
FROM _Sent s
WHERE s.EventDate > dateadd(d,-30,getdate())

The output of this query stored in a DE (Emails Sent)

2nd Query to find the openers in 30 days

SELECT
s.Subscriberkey,
s.Sent_Date,
o.EventDate AS Open_Date
From "Emails Sent" as s
INNER JOIN _Open o
ON s.Subscriberkey = o.Subscriberkey
WHERE o.IsUnique = 1 and
o.EventDate > dateadd(d,-30,getdate())

The output of this query stored in a DE (Emails Opens)

Both the queries ran successfully and I got some numbers but I would like to check if this is the right way to do it. I think both the Emails Sent & Emails Opens DE's has duplicate entries since a user could have received & opened multiple emails.

NOTE: Both the DE I have created doesn't have a primary key.

Answer 1

Try something like this, and in your target DE use the Subscriber Key as the Primary Key:

SELECT 
s.SubscriberKey, 
s.EventDate as SentDate, 
o.EventDate as OpenDate, 
FROM _Sent s LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
WHERE o.EventDate > dateadd(d,-30,getdate())