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.
The temporary table which probably ends up being a subquery would look similar to this:
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:
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 email@example.com instead of 2?
My understanding would be it is 1 because the last customer has firstname.lastname@example.org 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;email@example.com 2;1;firstname.lastname@example.org 3;1;email@example.com 4;2;firstname.lastname@example.org 5;2;email@example.com 6;3;firstname.lastname@example.org 7;3;email@example.com 8;4;firstname.lastname@example.org 9;4;email@example.com 10;4;firstname.lastname@example.org 11;4;email@example.com 12;5;firstname.lastname@example.org 13;6;email@example.com 14;7;firstname.lastname@example.org