Query: Deduplicate results (from _Click data view query)

Question

I'm trying to add click on a certain alias to a data extension for segmentation purposes, but I find my query erroring with the 'Violation of PRIMARY KEY constraint. Cannot insert duplicate key.' error. Probably because the same subscriber can have multiple clicks.

How can I extend this query so it only returns the latest click by each subscriber

SELECT c.SubscriberKey, 
m.EmailAddress, 
m.Country, 
m.Language, 
m.Gender, 
m.FirstName, 
m.LastName, 
m.Store, 
m.StoreId, 
c.LinkName
FROM _Click c
JOIN MasterSubscribers m on c.SubscriberKey = m.SubscriberKey
WHERE c.LinkName = 'WeLove_BornholmTable'
AND m.Consent_status = 'Confirmed' 
AND c.EventDate > dateadd(hh,-24,getdate())
AND c.SubscriberKey NOT IN
(SELECT j.SubscriberKey FROM JourneyDE_WeLove_BornholmTable j)

I've tried using DISTINCT and tried adding GROUP BY, but have only caused syntax errors so far. Please guide me. Thank you :)

Answer 1

If your target Data Extension's primary key is just Subscriber Key, I'd expect a primary key violation since you're joining with a one-to-many relationship (MasterSubscribers -> _Click).

I think you can achieve what you want without a JOIN:

select 
  m.SubscriberKey
, m.EmailAddress
, m.Country
, m.Language
, m.Gender
, m.FirstName
, m.LastName
, m.Store
, m.StoreId
, 'WeLove_BornholmTable' LinkName
from MasterSubscribers m 
where 
m.Consent_status = 'Confirmed' 
and exists (

  select top 1 c.*
  from _Click c
  where c.EventDate > dateadd(hh, -24, getdate())
  and c.subscriberkey = m.subscriberkey
  and c.LinkName = 'WeLove_BornholmTable'
  and c.isunique = 1

)
and not exists (
    select top 1 j.*
    from JourneyDE_WeLove_BornholmTable j
    where j.subscriberkey = m.subscriberkey
)

Also, your c0.EventDate > dateadd(hh, -24, getdate()) will only give you clicks after the current date and time 24 hours ago.

If you want clicks on that link in the last 24 hours, I'd suggest using something like this instead:

and c0.EventDate > convert(date, getDate()-1))

This will give any clicks after midnight yesterday.