Double Vision

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
UKG

Problem

Ahead of a CRM migration, the data quality team is hunting for email addresses that were entered against more than one account. For each email tied to multiple accounts, return the address, how many accounts carry it, and the earliest and most recent signup dates, with the most-repeated addresses first.

Before the records move, the ones wearing the same name twice have to surface.

Tables

users

Columns: user_id INTEGER, email TEXT, signup_date TEXT, username TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 4 of 4 rows
emailoccurrence_countearliest_signuplatest_signup
haruki@example.com22025-07-072026-09-02
hayley_b@example.com22024-07-272026-02-12
henry@example.com22023-05-252024-04-24
hiro@example.com22024-12-022025-05-25

Worked solution and explanation

What this problem really is

Strip the migration costume off and this is a 'collapse rows into groups, then keep only the crowded groups' question. Everyone writes GROUP BY email. Two things separate the clean answer from the messy one. First, the count filter has to run AFTER the rows are grouped, which means HAVING, not WHERE. Second, a pile of NULL-email rows is a missing-data defect, not a duplicate. Forget to exclude NULL and you hand the migration team a phantom duplicate that is really every account someone never gave an email: 16 accounts on this data, sitting at the top of the list.

Break down the requirements

Step 1: Group by email

The team wants addresses tied to more than one account, so the unit of the answer is the address. NULL is the absence of an address, not one, so a clump of NULL-email rows is a missing-data problem, not a duplicate-email one. Collapse the remaining rows by address with GROUP BY email.

Step 2: Filter to real duplicates

Drop missing addresses up front with WHERE email IS NOT NULL, then keep only the crowded groups with HAVING COUNT(*) > 1. The count condition has to be HAVING: WHERE runs before grouping, so it cannot see a per-group count.

Step 3: Extract the date boundaries and order

Inside each surviving group, COUNT(*) is the occurrence count, MIN(signup_date) is the earliest signup and MAX(signup_date) is the most recent. Order by occurrence_count DESC, then email ASC, so the worst offenders surface first and ties come back in a stable order.

The solution

Group, count, and filter for duplicates
SELECT email, COUNT(*) AS occurrence_count, MIN(signup_date) AS earliest_signup, MAX(signup_date) AS latest_signup FROM users WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1 ORDER BY occurrence_count DESC, email ASC

Common follow-up questions