Emails Used More Than Once
Problem
A table account(id, email) holds one row per sign-up, and a signup form without a uniqueness check has let some emails in several times. Write a query that returns every email that appears on more than one account, with the number of accounts that use it, as columns email and uses, sorted by email. Emails are compared exactly as stored.
Examples
Input: account = [(1, "a@x.io"), (2, "b@x.io"), (3, "a@x.io"), (4, "c@x.io"), (5, "b@x.io"), (6, "a@x.io")]
Output: [("a@x.io", 3), ("b@x.io", 2)]
Why: c@x.io is used once, so it is not a duplicateInput: account = [(1, "a@x.io"), (2, "b@x.io")]
Output: []
Why: edge case, no email repeats, so no row comes backHints
0 / 3
Rows that share an email have to be counted together, so the email is what the rows are grouped by.
WHERE runs before the grouping and cannot see a count. Another clause filters whole groups after they are formed.
GROUP BY email, select email and COUNT(*), keep the groups with HAVING COUNT(*) > 1, and finish with ORDER BY email so the order is defined.
Solution
GROUP BY email folds every set of rows that share an email into one group, and COUNT(*) counts the rows in it. The filter has to run after that fold, because a single row knows nothing about how many others share its email, which is exactly what HAVING is for: WHERE filters rows before grouping, HAVING filters groups after it. Without ORDER BY a database may return groups in any order, so the sort is part of the answer. The usual follow-up is to delete the extra accounts and keep the oldest one per email: DELETE FROM account WHERE id NOT IN (SELECT MIN(id) FROM account GROUP BY email). Grouping costs O(n) with hashing or O(n log n) with sorting, and an index on email lets the database read the groups already in order.
import sqlite3
QUERY = """
SELECT email, COUNT(*) AS uses
FROM account
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email
"""
def run(accounts):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE account (id INTEGER PRIMARY KEY, email TEXT)")
db.executemany("INSERT INTO account VALUES (?, ?)", accounts)
return db.execute(QUERY).fetchall()
print(run([(1, "a@x.io"), (2, "b@x.io"), (3, "a@x.io"), (4, "c@x.io"), (5, "b@x.io"), (6, "a@x.io")])) # -> [('a@x.io', 3), ('b@x.io', 2)]
print(run([(1, "a@x.io"), (2, "b@x.io")])) # -> []Stuck on the idea rather than the code? Aggregations & GROUP BY covers it.