SQL Keep Most Recent Duplicate

How would you write a SQL query to identify candidate records with duplicate email addresses and return the most recent registration for each email?

Assume duplicate detection uses exact, non-NULL email values. If registration timestamps tie, select the candidate with the highest candidate_id.

Output

1. One row per duplicated email address, excluding NULL emails
2. Columns: candidate_id, email, full_name, registered_at, and duplicate_count
3. Return the most recent registration for each duplicated email
4. Order by email ascending