Interview practice

SQL Deduplication Interview Questions

Practice DISTINCT, GROUP BY, ROW_NUMBER, duplicate audits, and choosing the keeper row.

Deduplication questions test whether you know what counts as a duplicate. Sometimes the answer is exact duplicate rows. More often, duplicates are repeated business entities: the same email, same account id, same session, or same page viewed multiple times.

The first question to ask is whether the task is an audit or a cleanup. An audit counts duplicates. A cleanup chooses which row to keep.

Common deduplication interview prompts

Find pages where pageviews exceed distinct sessions.
Count duplicate emails in an imported customer table.
Keep the newest row for each account.
Explain when DISTINCT hides a data-quality issue.
Remove duplicate event rows while preserving the original event count.

Duplicate audit

select path,
       count(*) as pageviews,
       count(distinct session_id) as sessions,
       count(*) - count(distinct session_id) as repeat_views
from pageviews
group by path
having count(*) > count(distinct session_id)
order by repeat_views desc, path

What matters

DISTINCT removes duplicate result rows, but it does not explain why duplicates exist. For interviews, a grouped audit is often stronger because it shows the raw count, the unique count, and the difference.

When you need one keeper row per entity, use row_number() with a clear ordering rule. The partition defines the duplicate group; the ordering defines which row survives.

Choose the keeper row

with ranked as (
  select *,
         row_number() over (
           partition by email
           order by signup_date desc, customer_id desc
         ) as rn
  from customers
)
select *
from ranked
where rn = 1

How to talk it through

Say: "I will define duplicates by email, rank rows inside each email group, keep the newest row, and audit how many rows were removed before deleting or overwriting anything."

If the table is an event log, be careful. Repeated events might be real behavior, not dirty data. Deduplicate only when the business definition says repeated rows are accidental.