In creating an audience for a Journey, I'm pulling everyone who already received one of our Welcome emails so they skip that step in the Journey. To do this, we've written the query below and it works for the most part.
SELECT SubscriberKey as EMAIL_ADDRESS_, CASE WHEN EMAILID = '14146' then 'TRUE' WHEN EMAILID = '3661' then 'TRUE' ELSE 'FALSE' END as WelcomeSent FROM _SENT s JOIN _JOB j on S.JobID = J.JobID WHERE SubscriberKey in (SELECT EMAIL_ADDRESS_ FROM [WELCOME])
The issue arises when someone received multiple emails (for example, a Welcome and an Abandoned Cart email) -- because a record appears multiple times in the _SENT table, the LAST email received will populate the value in WelcomeSent.
This means we get a few False-positives. That is, people who received the WELCOME but the data results shows as FALSE
How do we modify the code such that only the WELCOME email is considered in the query?
What complicates this further is that we have no idea if the WELCOME email was sent before or after other emails received by the subscriber.