First define what duplicate means, since exact copies and business duplicates are different problems. To find them, GROUP BY the intended key and filter with HAVING COUNT(*) greater than one. To remove them, use ROW_NUMBER partitioned by that key with a deterministic ORDER BY, then delete or exclude everything where the number is greater than one, keeping the most recent or most complete row.
Why interviewers ask this
Duplicate handling is a weekly task and a common source of inflated metrics. Interviewers want to see that you clarify the definition of duplicate before writing SQL, that you use a window function rather than a fragile self join, and that you pick which copy to keep deliberately rather than at random.
How to structure your answer
- Ask what counts as a duplicate before touching SQL.
- Detect with GROUP BY plus HAVING COUNT greater than one.
- Deduplicate with ROW_NUMBER over a partition and a stable sort.
- State the rule for which row survives and why.
- Investigate the upstream cause rather than only cleaning.
Example answer
I would ask what duplicate means here first, because a fully identical row usually means a load ran twice, whereas two rows for the same customer with different email addresses is a data entry problem that needs a business decision. To find them I group by the intended key and use HAVING COUNT greater than one, which also tells me the scale of the problem. For removal I use ROW_NUMBER partitioned by that key, ordered by updated_at descending with the primary key as a tiebreaker so it is reproducible, then keep row number one. If I am not allowed to delete, the same logic goes into a view. The part I try not to skip is asking where they came from. Cleaning duplicates every Monday morning is a symptom. Last time I traced it, a retried ingestion job was appending instead of merging, and fixing that took an hour and ended the weekly cleanup permanently.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How do you decide which of the duplicate rows to keep?
- How would you deduplicate when the key is fuzzy, like a name and address?
- What would you check upstream to stop duplicates recurring?
Related data analyst questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions