Shared Bike-Share Emails

A bike-share app is about to make email addresses unique. Before adding the constraint, the team needs to know which email addresses are already used by more than one rider account. Emails are already stored in lowercase, so an exact match is enough. Accounts created with phone sign-in have no email (NULL); missing emails are not duplicates and must be ignored.

Return email and account_count (how many accounts use that email) for every email shared by two or more accounts, one row per email, in any order. If there are no duplicates, return no rows.

Tables

rider_accounts — One row per rider account in the bike-share app.

Example

rider_accounts

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Select & Filter · problem 5 of 70 · SQLite

The idea

GROUP BY email collapses the table to one row per email value, and COUNT(*) then tells you how many accounts are in each group. Keeping only groups with more than one account is a condition on the group, so it goes in HAVING COUNT(*) > 1. WHERE runs before grouping and cannot see group sizes.

The NULLs need attention. Although NULL = NULL is not true in a comparison, GROUP BY places all NULLs into a single group. Two phone-only accounts would therefore show up as a NULL email with a count of 2. Filtering them out first with WHERE email IS NOT NULL removes that false alarm.

The alternative uses COUNT(email) instead of COUNT(*). COUNT(column) skips NULL values, so the NULL group has a count of 0 and fails the HAVING test on its own. Knowing the difference between COUNT(*) and COUNT(column) is worth the practice.

Solution

SELECT email,
       COUNT(*) AS account_count
FROM rider_accounts
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

Another way

SELECT email,
       COUNT(email) AS account_count
FROM rider_accounts
GROUP BY email
HAVING COUNT(email) > 1;

Why common answers fail

GROUP BY puts all NULL emails into one group and COUNT(*) counts them, so a NULL "duplicate" row appears.

SELECT email, COUNT(*) AS account_count FROM rider_accounts GROUP BY email HAVING COUNT(*) > 1;

The self-join produces one row per ordered pair of accounts, so an email used n times is counted n*(n-1) times.

SELECT a.email, COUNT(*) AS account_count FROM rider_accounts AS a JOIN rider_accounts AS b ON a.email = b.email AND a.account_id <> b.account_id GROUP BY a.email;

Counts distinct emails inside each group, which is always 1, so no duplicates are ever found.

SELECT email, COUNT(*) AS account_count FROM rider_accounts WHERE email IS NOT NULL GROUP BY email HAVING COUNT(DISTINCT email) > 1;

What the interviewer is checking

The classic check of WHERE versus HAVING, with a twist: how GROUP BY and COUNT treat NULLs.

Mistakes to avoid

• Putting the count condition in WHERE.
• Reporting the NULL group as a duplicated email.
• Using a self-join and getting inflated counts.

Follow-ups to expect

• List the account ids that share each email. (group_concat(account_id) in the same query)
• Keep only the oldest account per email. (ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_on, account_id))
• Treat emails as equal regardless of case. (GROUP BY LOWER(email))

In real work

Before adding a unique index or merging customer records, teams run exactly this query to find the conflicting values.

Practice next