0

I'm attempting to add leads and contacts into a DE from a synced data extension that receives contacts and leads when a quiz is completed. The quiz can be taken multiple times which is why there is a violation of the primary key. I'm a SQL newbie and am having difficulty finding a solution. Here's the SQL:

SELECT CON.Id AS Id, QU.Email_Address__c AS Email_Address, QU.Quiz_Name__c AS Quiz
FROM Quiz__c_Salesforce QU
INNER JOIN Contact_Salesforce CON
ON QU.Email_Address__c = CON.Email
WHERE Quiz_Name__c = 'Intro to IT: Cybersecurity'
UNION
SELECT LEAD.Id AS Id, QU.Email_Address__c AS Email_Address, QU.Quiz_Name__c AS Quiz 
FROM Quiz__c_Salesforce QU
INNER JOIN Lead_Salesforce LEAD
ON QU.Email_Address__c = LEAD.Email
WHERE Quiz_Name__c = 'Intro to IT: Cybersecurity'
2
  • I assume that Id is the primary key here? If people can enter multiple times this would obviously violate the primary key. What you can do depends on what you want to achieve. Do you need all entries for a single person or just the fact that a participated in given quiz, no matter how often it entered? Mar 19 '20 at 17:29
  • I only need on entry per person, no matter how often they entered.
    – Drewby123
    Mar 23 '20 at 14:52
0

I would suggest to add a timestamp field so you have some sort of control over which record you pick per person (say, the most recent).

Then you can work with SQL to achieve a deduplication using ROW_NUMBER to assign a rownumber, and partition by / order by to get the desired record.

Here is an example with "get the newest order", which similar to your usecase, e.g. get one (e.g. newest) quiz answer record:

SELECT o.SubscriberKey, o.Product, o.orderTimestamp
FROM (
  SELECT SubscriberKey, Product, orderTimestamp, ROW_NUMBER() OVER(Partition by SubscriberKey ORDER BY orderTimestamp DESC) AS Row_Number   
  FROM Orders) o
WHERE b.Row_Number = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.