Duplicate Records Interview Questions

If duplicate means the same email, I group by email and HAVING COUNT(*) > 1. That gives me which emails are duplicated. To see the actual rows I join back, or I use ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and keep rows where the number is greater than 1. To delete and keep one, I keep MIN(id) per email, or delete where ROW_NUMBER > 1. I would run a SELECT first, never a DELETE in an interview without saying I would take a backup. The real fix after cleanup is a unique constraint so the same duplicate cannot come back.

I would use a CTE with ROW_NUMBER() PARTITION BY the business key ORDER BY updated_at DESC, then delete where rn > 1. If I cannot use windows, I keep MIN(id) or MAX(updated_at) in a grouped subquery and delete ids not in that set. I would run a SELECT first in a transaction I can roll back. I would not DELETE FROM t WHERE id NOT IN (SELECT MIN(id)...) on a huge table without batching.

A unique constraint or unique index on the business key. If the key is nullable or composite, I define it carefully. App-level checks are not enough under concurrency. I mention ON CONFLICT / MERGE for upserts. If they already have duplicates, I clean first, then add the constraint. Adding unique on dirty data just fails the migration.

I would normalize: LOWER(TRIM(email)) as the grouping key. I might also collapse dots for some providers if product asks. I would not unique-index the raw column if the app stores mixed case. I show a GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1 query. Data quality is often a cleaning function plus a constraint, not only GROUP BY id.

GROUP BY every column and HAVING COUNT(*) > 1. Or hash the row if the engine can. Deleting then is harder because I cannot say 'keep min id'. I might add a ctid / physical identifier in Postgres, or load into a new table SELECT DISTINCT. I would tell them this is why tables need a primary key. No-id duplicates are a mess and I would say so.