Marketing cloud SQL Query Deduplication

Question

I am trying to deduplicate my entry data extension based on the completeness of field values. For Instance, i have the a data extension with same email address, the record that has maximum field values filled in, that should be selected.

For instance, if I have three records and I have 4 columns, then the record which has values for all the columns or maximum columns should be selected.

Answer 1

I think you would have to separate this into 2 queries. One to a staging table and then another to overwrite the final file.

Query 1:

SELECT EmailAddress,
       Field1,
       Field2,
       Field3,
       Field4,
       CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL AND Field3 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL AND Field4 != '' THEN 1 ELSE 0 END AS Field5
FROM TableA

This will fill in the total number of not null and not empty columns in your DE. You would then use a second query to pull only those with the highest 'Field5' value per email address like so:

SELECT t.EmailAddress,
t.Field1,
t.Field2,
t.Field3,
t.Field4
FROM [Staging_DE] t
INNER JOIN (
    SELECT a.EmailAddress,
    MAX(a.Field5) as Field5
    FROM [Staging_DE] a
    GROUP BY EmailAddress
) mx
ON mx.EmailAddress = t.EmailAddress AND mx.Field5 = t.Field5

Where Staging_DE is the target of your Query 1.

You can technically have this all be inside a single query and just replace Staging_DE with a subquery using Query 1.

E.g.

SELECT t.EmailAddress,
t.Field1,
t.Field2,
t.Field3,
t.Field4
FROM (
       SELECT EmailAddress,
       Field1,
       Field2,
       Field3,
       Field4,
       CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL AND Field3 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL AND Field4 != '' THEN 1 ELSE 0 END AS Field5
       FROM TableA
) t
INNER JOIN (
    SELECT a.EmailAddress,
    MAX(a.Field5) as Field5
    FROM (
       SELECT EmailAddress,
       Field1,
       Field2,
       Field3,
       Field4,
       CASE WHEN Field1 IS NOT NULL AND Field1 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL AND Field2 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL AND Field3 != '' THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL AND Field4 != '' THEN 1 ELSE 0 END AS Field5
       FROM TableA
    ) a
    GROUP BY EmailAddress
) mx
ON mx.EmailAddress = t.EmailAddress AND mx.Field5 = t.Field5

Answer 2

Could you use the LEN() function to determine the length of the max field value. Something like this?

Select * From [Table Name] Where LEN(Field Name) = 250 (or whatever the max character length is)

Answer 3

I have only tried this in SQL Management Studio, but should work in Marketing Cloud too. It should be enough to get you going.

SELECT ContactKey, PopulatedColumns
    FROM (
    SELECT 
        ContactKey,
        (CASE WHEN COALESCE(Column1, '') != '' THEN 1 ELSE 0 END +
        CASE WHEN COALESCE(Column2, '') != '' THEN 1 ELSE 0 END +
        CASE WHEN COALESCE(Column3, '') != '' THEN 1 ELSE 0 END +
        CASE WHEN COALESCE(Column4, '') != '' THEN 1 ELSE 0 END) AS PopulatedColumns 
        FROM Table
    ) t
ORDER BY PopulatedColumns DESC