Nivel 8 · Desafío final: una vista limpia de clientes

La limpieza rinde cuando una tabla limpia alimenta muchos informes. Este desafío final construye una vista limpia de clientes una sola vez, con todas las reglas del capítulo, y responde tres preguntas a partir de ella.

La vista limpia de clientes

Escribe las reglas antes del SQL:

• Una persona es un email limpio, LOWER(TRIM(email)).
• El registro que sobrevive es el alta más temprana, con empates resueltos por el customer_id más bajo.
• El estado es UPPER(TRIM(state)). La fuente de referencia es COALESCE(referral_source, 'unknown').
• Los ingresos vienen solo de pedidos completados y de líneas con cantidad positiva.

WITH clean_customers AS (
  SELECT customer_id, UPPER(TRIM(state)) AS state
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY LOWER(TRIM(email)) ORDER BY signup_date, customer_id) AS rn
    FROM customers
  ) AS ranked
  WHERE rn = 1
)

Cada paso repite esta CTE. En el trabajo sería una vista guardada, y cada panel seleccionaría de ella.

Paso 1: ¿cuánto cambió la limpieza?

Antes de reportar, muestra el efecto de las reglas. De 1,732 filas de consumidores, 1,716 son personas distintas. De 286 filas de empresas, 284. Reportar esto junto a los números hace que la limpieza sea auditable.

Paso 2: ingresos por estado limpio

Agrega primero los ingresos por cliente y luego haz el join: una fila por cliente en ambos lados, así que no hay multiplicación de filas. Con los valores de estado en bruto, California aparecería como CA, ' CA', 'ca' y 'CA ', con 234, 2, 2 y 2 compradores. Tras limpiar es una sola fila, el estado top con 240 clientes compradores.

Paso 3: ¿qué fuentes se convierten en compradores?

people cuenta a todos en la vista limpia; buyers cuenta a los que tienen al menos un pedido completado. Un LEFT JOIN conserva a las personas que nunca compraron; COUNT(b.customer_id) se salta sus NULL. El grupo unknown sigue siendo el más grande con 648 personas, por debajo de las 666, porque los 18 registros duplicados no tenían fuente de referencia. NULLIF protege la división por si algún grupo quedara vacío.

Errores comunes

• Reglas inconsistentes entre pasos. Si el paso 2 usa el estado en bruto, su total ya no coincide con el del paso 1.
• Unir pedidos antes de deduplicar. Aquí funciona solo porque los duplicados no tienen pedidos. Con datos reales, reasigna primero los ids descartados al id conservado.
• INNER JOIN para compradores. Elimina a los que no compraron, así que todas las fuentes muestran 100%.

En SQL Server

La misma consulta funciona con ISNULL o COALESCE y TOP 10 en lugar de LIMIT 10 (SELECT TOP 10 c.state, ... ORDER BY net_revenue DESC). Guarda la CTE como una vista con CREATE VIEW clean_customers AS SELECT .... Una vista no puede contener el ORDER BY sin TOP.