OR

Breaking this down

1. After "partition by" is the column (or columns) that will hold the unique criteria.

2. After "order by" are the columns to order your results by. Considering that if you want the first time someone opened an email then you will want to sort by EventDate in order or oldest date to newest (ASC - Ascending).

Notes:

3. You can comma separate the partition by columns and order by columns if you need to have multiple criteria

4. Consider your primary key(s) in your Data Extension, For the above to work you would need PKs for SubscriberKey and EmailName

Breaking this down

The actual columns you create in your sending data extension are debatable, but it is important when querying for a resend list of for Opened/Engaged that you exclude consideration of clicks to unsubscribe and view the privacy policy.

SELECT

c.id as contactid,

b.emailaddress,

'Held' AS status

FROM Contact_Salesforce c

INNER JOIN DATA_EXTENSION_BOUNCES b

ON c.email = b.emailaddress

WHERE b.emailaddress is not null

If there are a many columns in a Data Extension and you want to identify those with no values (could be missing from a query) here is one way to go about it.

1. Copy the Data Extension and populate it with one value from the original DE. (ex. SELECT TOP 1 * FROM ORIGINAL_DE)

2. Export the single record and delete the new DE (you only needed it to get all the column names)

3. Open the downloaded file in excel, copy all of the column names and paste the transposed (so instead of across they are in rows).

4. Insert these 2 functions in the 2nd and 3rd columns:

   1. Colmun B: =CONCATENATE(", pp.",A1)

   2. Column C: =CONCATENATE(" , SUM(CASE WHEN p.",A1," IS NOT NULL THEN 1 ELSE 0 END) AS ",A1)

5. Build the below SQL query with the data you composed in Excel (line 3 comes from Column C, line 7 is column B):

1. SELECT
2. Columns
3. , SUM(CASE WHEN p.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END) AS COLUMN_NAME
4. FROM (
5. SELECT
6. 'one' as Columns
7. , pp.COLUMN_NAME
8. FROM ORIGINAL_DE pp
9. ) p
10. GROUP BY
11. Columns