Skip records with duplicate Primary Key while querying data using SQL activity in SFMC

Question

I am trying to query some specific email activities using SQL Activity and Data Views in SFMC. I am trying to fetch data from Subscriber, Sent and Job data view. I need EmailName, EmailAddress, SubscriberKey,EventDate and JobID field in the query results. Following is the the SQL Activity:

SELECT sub.SubscriberKey AS SubscriberKey,
       sub.EmailAddress AS EmailAddress,
       job.EmailName AS EmailName,
       sen.EventDate AS ActivityDate,
       sen.JobID AS JobID
FROM   ent._Subscribers AS sub
       INNER JOIN _Sent AS sen
               ON sub.SubscriberKey = sen.SubscriberKey
       INNER JOIN _Job AS job
               ON sen.JobID = job.JobID
WHERE  job.EmailName LIKE '%ABC%'

I am using SubScriberKey and JobId as a primary key in target data extension. UPDATE has been chosen as Data Action on the SQL Activity. After executing SQL query found that data view has some duplicate records i.e. same email has been sent to same subscriber as part of same send job so SubscriberKey and JobID is duplicate on some records. Because of this SQL query fails with "Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object" error.

I basically don't want the duplicate records in my query result. Is there a way to skip duplicate records in query results?

Answer 1

The primary key on _Sent is [JobID,ListID,BatchID,SubscriberID], so you can either change the primary key on your results table to avoid the error, you can use a GROUP BY clause in your query or use a PARTITION BY clause in a subquery and restrict rows returned after partitioning.

GROUP BY

SELECT sub.SubscriberKey AS SubscriberKey,
       sub.EmailAddress AS EmailAddress,
       job.EmailName AS EmailName,
       MAX(sen.EventDate) AS ActivityDate,
       sen.JobID AS JobID
FROM   ent._Subscribers AS sub
       INNER JOIN _Sent AS sen
               ON sub.SubscriberKey = sen.SubscriberKey
       INNER JOIN _Job AS job
               ON sen.JobID = job.JobID
WHERE  job.EmailName LIKE '%ABC%'
GROUP BY
        sub.SubscriberKey,
        sub.EmailAddress,
        job.EmailName,
        sen.JobID

PARTITION BY

SELECT x.*
FROM 
    (
      SELECT
              ROW_NUMBER() OVER(PARTITION BY sub.SubscriberKey,sen.JobID ORDER BY sen.EventDate DESC) AS RowNumber,
              sub.SubscriberKey AS SubscriberKey,
              sub.EmailAddress AS EmailAddress,
              job.EmailName AS EmailName,
              sen.EventDate AS ActivityDate,
              sen.JobID AS JobID
      FROM    ent._Subscribers AS sub
              INNER JOIN _Sent AS sen
                     ON sub.SubscriberKey = sen.SubscriberKey
              INNER JOIN _Job AS job
                     ON sen.JobID = job.JobID
      WHERE   job.EmailName LIKE '%ABC%'    
    ) AS x
WHERE 
  x.RowNumber = 1

Answer 2

same email has been sent to same subscriber as part of same send job so SubscriberKey and JobID is duplicate on some records

Your target data extension would need to have a PrimaryKey on EventDate, JobId, SubscriberKey.

If that is the case and you indeed have one email send at multiple times to a single subscriber on the same EventDate with the same JobId, which I find unlikely, you can use ROW_NUMBER.

The following will select the first occurrence on the combination of JobId, SubscriberKey and EventDate. If you want the last occurrence, you need to change the ORDER BY from ASC to DESC.

SELECT 
    R.SubscriberKey
    , R.EmailAddress
    , R.EmailName
    , R.EventDate AS ActivityDate
    , R.JobID

FROM (  
        SELECT 
            sub.SubscriberKey
            , sub.EmailAddress
            , job.EmailName
            , sen.EventDate
            , sen.JobID
            , ROW_NUMBER() OVER(PARTITION BY sen.EventDate,sub.SubscriberKey,sen.JobID ORDER BY sen.EventDate ASC) RN

        FROM ent._Subscribers AS sub

        INNER JOIN _Sent AS sen
            ON sub.SubscriberKey = sen.SubscriberKey

        INNER JOIN _Job AS job
            ON sen.JobID = job.JobID

        WHERE  job.EmailName LIKE '%ABC%'

) R

WHERE R.RN = 1