Error with Duplicate Primary key in Marketing Cloud SQL

Question

We have multiple newsletters that generate leads for marketing emails. I'm trying to automate the process of consolidating those leads into one master data extension after they have been on a newsletter list for 30 days.

Currently, the sql I'm using looks like this:

SELECT 
           L.CreatedDate
          ,L.EmailAddress
          ,L.ListID
          ,L.Status
FROM ent._ListSubscribers L
where l.ListID   in  (20798913, 20757098, 20828774, 20233021, 20551644, 18973750, 20549665, 20540177, 20712964, 20233025, 19009581, 19009580, 20427611, 19649300, 20233035, 20585536, 20468748, 19009576, 19009574, 19009571, 19009570) /*All_Pub_Lists*/
and
Status   =  'Active'
and
CreatedDate between (dateAdd(dd, -31, dateAdd(dd, 0, getDate()))) and (dateAdd(dd, -30, dateAdd(dd, 0, getDate())))
and
l.EmailAddress not in (select EmailAddress from ent.Master_Prospect_Copy)

The query activity is set to append new data in the target data extension. I was able to run the query a single time and it produced the expected results. However, now whenever the query activity runs it fails due to a Duplicate Primary Key error.

The error makes sense, as people are free to sign up to newsletters whenever they'd like, so they end up on multiple lists with multiple created dates. I tried to removed the duplicates with the

and
l.EmailAddress not in (select EmailAddress from ent.Master_Prospect_Copy)

but it hasn't worked.

Does anyone know how I might continue to append new data, but avoid triggering the Duplicate Primary Key error?

Best, Jeff

Answer 1

Because you are only interested in distinct leads I would use this:


SELECT 
           Row_Number() Over(PARTITION BY EmailAddress ORDER BY CreatedDate DESC) AS Row
           ,L.CreatedDate
          ,L.EmailAddress
          ,L.ListID
          ,L.Status
FROM ent._ListSubscribers L
where l.ListID   in  (20798913, 20757098, 20828774, 20233021, 20551644, 18973750, 20549665, 20540177, 20712964, 20233025, 19009581, 19009580, 20427611, 19649300, 20233035, 20585536, 20468748, 19009576, 19009574, 19009571, 19009570) /*All_Pub_Lists*/
and
Status   =  'Active'
and
CreatedDate between (dateAdd(dd, -31, dateAdd(dd, 0, getDate()))) and (dateAdd(dd, -30, dateAdd(dd, 0, getDate())))
and
Row = 1

Should solve it