Identifying and Removing Duplicate Rows While Keeping One

-- Step 1: Identify duplicates (e.g., based on email and name)
SELECT
    email, first_name, last_name, COUNT(*)
FROM
    users
GROUP BY
    email, first_name, last_name
HAVING
    COUNT(*) > 1;

-- Step 2: Delete duplicates, keeping the one with the lowest ID (or highest, etc.)
DELETE FROM users
WHERE user_id IN (
    SELECT user_id
    FROM (
        SELECT
            user_id,
            ROW_NUMBER() OVER (PARTITION BY email, first_name, last_name ORDER BY user_id) as rn
        FROM
            users
    ) AS subquery
    WHERE rn > 1
);

How it works: To maintain data cleanliness, it's often necessary to remove duplicate rows while ensuring at least one unique record remains. The first step identifies groups of rows that share the same values for specified columns (e.g., `email`, `first_name`, `last_name`). The second step uses a Common Table Expression (or subquery in this case) with the `ROW_NUMBER()` window function. `ROW_NUMBER()` assigns a sequential integer to rows within each partition (defined by the duplicate columns), ordered by a unique identifier like `user_id`. By deleting rows where `rn > 1`, all but the 'first' instance of each duplicate set are removed.