Remove Duplicate Subscribers
Hard · Delete
To delete duplicates, first decide which row of each group survives, then delete the rest. A window function inside a subquery is a clean way to do it: number the rows of each group with ROW_NUMBER() and delete every row whose number is above 1.
The subscribers table has duplicates: two emails are the same if they match after lower(trim(email)) (so Ana@Mail.com and ana@mail.com are the same person). For each email, keep only the row with the latest signed_up_at; if two rows have the same latest signed_up_at, keep the one with the higher id. Delete every other row. Do not change the rows you keep.
The table is checked afterwards with SELECT id, email FROM subscribers ORDER BY id.