Write a query that finds and removes duplicate rows

First find the duplicates by grouping on the columns that define "the same row":

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

To delete, keep one row per group and remove the rest. ROW_NUMBER() numbers the rows inside each duplicate group, and everything after the first is extra:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t
  WHERE rn > 1
);

The ORDER BY id keeps the oldest row, change it to keep the newest. The extra derived table is needed in MySQL, which refuses to delete from a table it also reads in a plain subquery.

Before deleting, run the inner SELECT and check the count, and do it inside a transaction or on a backup. Then stop the problem at the source:

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Fix the cause with a unique constraint, otherwise the duplicates return.