Keep exactly ONE copy of each fully-duplicate row in dupes (rows where every column is identical). Use a stringified timestamp for grading stability.
Sort by customer_id then loaded_ts ascending.
Returncustomer_idemailphonestrftime(loaded_ts, '%Y-%m-%d %H:%M:%S') AS loaded_ts
About this problem
This problem practises true deduplication: removing rows that are exact copies of one another, as happens when a load job retries and writes the same record twice. In dupes, a duplicate means every column matches – customer_id, email, phone and loaded_ts. The business question is not “what is each customer’s current state?” but “which rows are accidental repeats?”, so a customer can legitimately keep several rows if they differ in any column. You keep one copy of each distinct row and return loaded_ts as a formatted string.
The tempting mistake is to deduplicate by key instead of by whole row: ROW_NUMBER() OVER (PARTITION BY customer_id ...) with a filter on the first row, or DISTINCT over just customer_id and email. That answers a different question – latest-row selection – and throws away genuine records. In the sample, c_001 has three rows with different emails, phones and load times; none is a copy of another, so all three must survive, while c_003 has two identical rows that should become one. Rows that match on everything except loaded_ts, like c_002’s two loads, are not full duplicates either. The other thing to watch is NULLs: c_001 and c_005 have blank phone or email values, and a hand-written equality comparison treats NULL as never equal to NULL, so rows that are otherwise identical but share a blank are not recognised as copies.
Read the lesson: Deduplication & Latest Row (P18) →