cleaning_queries.sql

-- ============================================
-- DATA CLEANING PROJECT
-- ============================================

-- Crear tabla con datos sucios
CREATE TABLE clientes_raw (
    id INT,
    nombre VARCHAR(50),
    email VARCHAR(100),
    ciudad VARCHAR(50),
    edad INT
);

-- Insertar datos con problemas
INSERT INTO clientes_raw VALUES
(1, 'Juan Perez', 'juan@mail.com', 'CDMX', 28),
(2, 'Ana Lopez', 'ana@mail.com', 'Guadalajara', NULL),
(3, 'Carlos Ruiz', 'carlos@mail.com', 'cdmx', 35),
(4, 'Maria Torres', 'maria@mail', 'Monterrey', 40), -- email inválido
(5, 'Luis Gomez', 'luis@mail.com', 'CDMX', 28),
(5, 'Luis Gomez', 'luis@mail.com', 'CDMX', 28), -- duplicado
(6, NULL, 'sin_nombre@mail.com', 'Puebla', 22);

-- ============================================
-- LIMPIEZA DE DATOS
-- ============================================

-- 1. Eliminar duplicados
WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
    FROM clientes_raw
)
DELETE FROM cte WHERE rn > 1;

-- 2. Manejo de valores NULL (edad)
UPDATE clientes_raw
SET edad = (SELECT AVG(edad) FROM clientes_raw WHERE edad IS NOT NULL)
WHERE edad IS NULL;

-- 3. Normalizar ciudades (mayúsculas)
UPDATE clientes_raw
SET ciudad = UPPER(ciudad);

-- 4. Identificar emails inválidos
SELECT *
FROM clientes_raw
WHERE email NOT LIKE '%@%.%';

-- 5. Eliminar registros sin nombre
DELETE FROM clientes_raw
WHERE nombre IS NULL;

-- ============================================
-- RESULTADO FINAL
-- ============================================

SELECT * FROM clientes_raw;