SQL Deduplication Interview Questions

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

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

1. Find pages where pageviews exceed distinct sessions.

   Your answer Try it in the playground →
   Reveal a model answer

   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

   An audit, not a cleanup: compare the raw count with the distinct count and keep the paths where they differ. Repeat views are real behavior here, so the answer describes them rather than deleting them.

2. Count duplicate emails in an imported customer table.

   Your answer Try it in the playground →
   Reveal a model answer

   with imported as (
     select email from customers
     union all
     select 'theo.brandt@example.com'
   )
   select lower(trim(email)) as email,
          count(*) as copies
   from imported
   group by lower(trim(email))
   having count(*) > 1
   order by copies desc, email

   Group by the normalized email and keep groups with more than one row. The CTE stands in for the import by adding one repeat. Normalizing with lower() and trim() matters: the same address typed with different casing is still a duplicate.

3. Keep the newest row for each account.

   Your answer Try it in the playground →
   Reveal a model answer

   with ranked as (
     select user_id,
            session_id,
            session_date,
            row_number() over (
              partition by user_id
              order by session_date desc, session_id desc
            ) as rn
     from sessions
     where user_id is not null
   )
   select user_id, session_id, session_date
   from ranked
   where rn = 1
   order by user_id

   The partition defines the duplicate group and the ORDER BY picks the keeper, newest first with a tie-breaker. Audit how many rows the filter drops before you delete anything.

4. Explain when DISTINCT hides a data-quality issue.

   Your answer
   Reveal a model answer

   When duplicates come from a bad join or a double load, DISTINCT makes the output look right while the cause stays. Worse, it can merge rows that differ only in a column you did not select. Find why rows repeat, fix the join or the load, and use DISTINCT only when repeats are expected.

5. Remove duplicate event rows while preserving the original event count.

   Your answer Try it in the playground →
   Reveal a model answer

   with loaded as (
     select * from events
     union all
     select * from events where event_id = 1
   )
   select count(*) as loaded_rows,
          count(distinct event_id) as distinct_events,
          count(*) - count(distinct event_id) as duplicate_rows
   from loaded

   Record the counts before removing anything, so the original total is on the record next to the cleaned one. The CTE stands in for a load that ran twice for one event. Decide what makes two rows the same event, here event_id, then keep one row per key with select distinct or row_number() = 1.

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.

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.

Practice next

Work through the pageview deduplication audit, review window functions, and keep the UNION ALL duplicate guide nearby for set-operation questions. The deduplication reference covers deleting copies in place and keeping them out with a key.