Find and delete duplicate rows safely

-- PostgreSQL: remove duplicate users in one transaction, keeping one row each.
BEGIN;

-- 1. Preview the rows that would be deleted.
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY lower(email)
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM users
)
SELECT id, email FROM ranked WHERE rn > 1;

-- 2. Delete exactly those rows; the newest row per email wins.
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY lower(email)
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

COMMIT;