Mobile Numbers Shared by Wallet Accounts — SQL Easy Problem

Problem statement

The campus canteen's prepaid wallet asks for a mobile number at sign-up. The number is optional, and nothing stops two accounts from giving the same one.

Return every mobile number used by more than one account, in a column named mobile, listing each such number once. Accounts without a mobile number (NULL) never count — a missing number is not a shared number. Return the rows in any order.

Tables

Table: WalletAccount

Primary key: account_id.

One row per wallet. mobile is a 10-digit number stored as text, or NULL if none was given.

Examples

Example 1

WalletAccount

Output

How to solve Mobile Numbers Shared by Wallet Accounts

Finding values that repeat is the classic use of GROUP BY … HAVING. Grouping by mobile puts all the accounts that share a number into one group; COUNT(*) is the group's size, and HAVING COUNT(*) > 1 keeps the numbers used more than once. Each group yields one row, so every shared number is listed once however many accounts use it.

The NULLs need a decision. GROUP BY treats all NULLs as one group, so two accounts without a number would form a group of size 2 and print a NULL row — but a missing number is not a shared number. Filtering them out first with WHERE mobile IS NOT NULL removes them before grouping. Counting the column instead of the rows does the same job: COUNT(mobile) skips NULLs, so the NULL group always counts 0.

A self join finds the duplicates another way — pair each account with a different account holding the same number, then DISTINCT the numbers — and NULLs drop out because NULL = NULL is not true. It compares pairs, so it is quadratic in the size of a group, while grouping is one pass (or a sort) over the table.

Reference solution (MySQL)

SELECT mobile
FROM WalletAccount
WHERE mobile IS NOT NULL
GROUP BY mobile
HAVING COUNT(*) > 1

Another way

SELECT mobile FROM WalletAccount GROUP BY mobile HAVING COUNT(mobile) > 1

Another way

SELECT DISTINCT a.mobile FROM WalletAccount a JOIN WalletAccount b ON a.mobile = b.mobile AND a.account_id <> b.account_id