Delete Duplicate Rows and Keep One

-- Keeps the row with the lowest id for each email and deletes the rest.

-- Step 1: preview exactly which rows would be deleted.
SELECT id, email
FROM (
    SELECT id, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
) AS ranked
WHERE rn > 1;

-- Step 2: delete them (works in SQL Server and PostgreSQL).
DELETE FROM users
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM users
    ) AS ranked
    WHERE rn > 1
);

-- SQL Server shortcut: delete straight through a CTE.
-- WITH ranked AS (
--     SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
--     FROM users
-- )
-- DELETE FROM ranked WHERE rn > 1;