1

I am kinda stuck with a query that should increase dataquality of subscribers. I want to get all subscribers that have the same email-adress while they have different customer-numbers.

Here is an example of the test dataset that I set up.

enter image description here

The temporary table which probably ends up being a subquery would look similar to this:

enter image description here

Or this:

enter image description here

Probably with a having clause of CustomerCount > 1 you would then ignore those that have a customercount of 1. For all these subscribers I would like to get every column in the original dataset (it hs more column than displayed here).

This would look like:

enter image description here

How do I write a query or multiple queries for this?

Side Question: Another question just to get a better understanding of the group by:

SELECT Count(Email) as CustomerCount, Email
FROM [SubscriberData]
GROUP BY Email, CustomberNumber

Why does this query returns the customerCount of 1 for Email f@salesforce.com instead of 2?

My understanding would be it is 1 because the last customer has f@salesforce.com while it would have been 2 before when it passed customer 4.

For those who want to test the test-dataset:

subscriberKey;customerNumber;email
1;1;a@salesforce.com
2;1;a@salesforce.com
3;1;b@salesforce.com
4;2;c@salesforce.com
5;2;d@salesforce.com
6;3;b@salesforce.com
7;3;d@salesforce.com
8;4;e@salesforce.com
9;4;e@salesforce.com
10;4;f@salesforce.com
11;4;f@salesforce.com
12;5;f@salesforce.com
13;6;f@salesforce.com
14;7;f@salesforce.com
Improve this question
6
  • It would be nice if the person that downvoted the question leaves atleast a description why. The only thing that I know of would be moving this towards dba.stackexchange.com but since marketing cloud is only able to perform a subset of all available functions of t-sql i believe that it is better suited inside this forum, because otherwise you will most likely get answers from dba.stackexchange that will not work on the marketing-cloud plattform.
    – Johannes Schapdick
    Jul 3, 2019 at 12:02
  • 2
    Your side question -- it returns multiple rows for f@salesforce.com because you're also grouping by CustomerNumber. If you include CustomerNumber you can see why you get that count: i.imgur.com/UBsaYoz.png
    – Adam Spriggs
    Jul 3, 2019 at 13:34
  • Why isn't a@salesforce.com included in your desired output table?
    – Adam Spriggs
    Jul 3, 2019 at 13:40
  • I still am not quite getting exactly what you are looking for in final output. What is the main goal of this? Get number of records that have same email address but not same customernumber?
    – Gortonington
    Jul 3, 2019 at 16:05
  • The main goal is to detect bad quality. If there is are two or more customers that share the same email, one of them is most likely to be obsolete. That’s why we wanna check these records
    – Johannes Schapdick
    Jul 3, 2019 at 16:55

1 Answer 1

Reset to default
1

If you want to know how many Subscribers have the same email address, you can do this with sub queries

SELECT c.subscriberKey
      ,c.customerNumber
      ,c.email
      ,c.CustomerCount
FROM (
    SELECT a.subscriberKey
          ,a.customerNumber
          ,a.email
          ,(
            SELECT COUNT(*)
            FROM [SubscriberData] AS b
            WHERE a.email = b.email
          ) AS CustomerCount
    FROM [SubscriberData] AS a
) AS c
WHERE c.CustomerCount> 1

enter image description here

Improve this answer

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.