E-mails usados mais de uma vez
A missão
Problema
Uma tabela account(id, email) guarda uma linha por cadastro, e um formulário de cadastro sem verificação de unicidade deixou alguns e-mails entrarem várias vezes. Escreva uma consulta (query) que retorne todo e-mail que aparece em mais de uma conta, com o número de contas que o usam, nas colunas email e uses, ordenado por e-mail. Os e-mails são comparados exatamente como estão armazenados.
Exemplos
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)]
Por quê: c@x.io é usado uma vez, então não é duplicado
Input: account = [(1, "a@x.io"), (2, "b@x.io")]
Output: []
Por quê: edge case: nenhum e-mail se repete, então nenhuma linha volta
Dicas
0 / 3
Linhas que compartilham um e-mail precisam ser contadas juntas, então é pelo e-mail que as linhas são agrupadas.
O WHERE roda antes do agrupamento e não enxerga uma contagem. Outra cláusula filtra grupos inteiros depois que eles são formados.
Faça GROUP BY email, selecione email e COUNT(*), mantenha os grupos com HAVING COUNT(*) > 1 e termine com ORDER BY email para que a ordem fique definida.
Restrições
- emails are compared exactly as stored
Solução
GROUP BY email junta cada conjunto de linhas que compartilham um e-mail em um único grupo, e COUNT(*) conta as linhas dele. O filtro precisa rodar depois desse agrupamento, porque uma linha sozinha não sabe nada sobre quantas outras compartilham o e-mail dela, e é exatamente para isso que serve o HAVING: o WHERE filtra linhas antes do agrupamento, e o HAVING filtra grupos depois dele. Sem ORDER BY, um banco de dados pode retornar os grupos em qualquer ordem, então a ordenação faz parte da resposta. A pergunta seguinte costuma ser como apagar as contas extras e manter a mais antiga de cada e-mail: DELETE FROM account WHERE id NOT IN (SELECT MIN(id) FROM account GROUP BY email). Agrupar custa O(n) com hashing ou O(n log n) com ordenação, e um índice em email permite que o banco leia os grupos já em ordem.
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")])) # -> []Travou na ideia, e não no código? Agregações e GROUP BY explica isso.