Duplicates rarely look identical. Amara.Okafor@example.com and amara.okafor@example.com are the same person, so deduplication starts by deciding what makes two rows "the same", usually a cleaned key such as LOWER(TRIM(email)).
Then pick which copy to keep with ROW_NUMBER:
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY raw_id DESC) AS rn
FROM signups_raw
WHERE email IS NOT NULL
)
SELECT * FROM ranked WHERE rn = 1;
PARTITION BY groups the copies; the ORDER BY decides which one is first (here the latest import). COUNT(*) OVER (PARTITION BY key) shows every row of a duplicated key, which is useful for reviewing before deleting.
Rows without a key (no email) need a decision of their own: they aren't duplicates of each other just because they're all NULL.