Correos usados más de una vez
La misión
Problema
Una tabla account(id, email) guarda una fila por cada cuenta creada, y un formulario para crear cuentas sin control de unicidad dejó entrar algunos correos varias veces. Escribe una consulta (query) que devuelva cada correo que aparece en más de una cuenta, junto con la cantidad de cuentas que lo usan, como columnas email y uses, ordenadas por correo. Los correos se comparan exactamente como están guardados.
Ejemplos
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 se usa una sola vez, así que no es un duplicado
Input: account = [(1, "a@x.io"), (2, "b@x.io")]
Output: []
Por qué: caso límite: ningún correo se repite, así que no vuelve ninguna fila
Pistas
0 / 3
Las filas que comparten correo se tienen que contar juntas, así que el correo es lo que define cómo se agrupan las filas.
WHERE se ejecuta antes de agrupar y no puede ver un conteo. Otra cláusula filtra grupos completos después de que se forman.
Haz GROUP BY email, selecciona email y COUNT(*), conserva los grupos con HAVING COUNT(*) > 1 y termina con ORDER BY email para que el orden quede definido.
Restricciones
- emails are compared exactly as stored
Solución
GROUP BY email junta en un grupo cada conjunto de filas que comparten correo, y COUNT(*) cuenta las filas que hay en él. El filtro tiene que ejecutarse después de esa agrupación, porque una fila sola no sabe cuántas otras comparten su correo, y para eso existe justamente HAVING: WHERE filtra filas antes de agrupar, HAVING filtra grupos después. Sin ORDER BY, una base de datos puede devolver los grupos en cualquier orden, así que el ordenamiento es parte de la respuesta. La pregunta de seguimiento (follow-up) habitual es borrar las cuentas sobrantes y conservar la más antigua de cada correo: DELETE FROM account WHERE id NOT IN (SELECT MIN(id) FROM account GROUP BY email). Agrupar cuesta O(n) con hashing u O(n log n) ordenando, y un índice sobre email le permite a la base de datos leer los grupos ya en orden.
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")])) # -> []¿Lo que te frena es la idea y no el código? Agregaciones y GROUP BY lo explica.