Deduplicate Leads With Latest Record

Write a SQL query to identify duplicate leads in a marketing database and return the most recent record for each lead at Pluralsight.

Treat nonblank email addresses as duplicates when they match after trimming whitespace and ignoring case. Exclude blank and NULL email addresses, and use lead_id as the tie-breaker when timestamps match.

Output

1. One row for each duplicate email group, containing lead_id, email, first_name, source, and created_at
2. Return only the most recent record for each group
3. Order by normalized email, then most recent created_at descending and lead_id descending