Nivel 7 · Desafío final: el informe anual del estado de los clientes

Este desafío final convierte tres operaciones de conjuntos en el puente de clientes que usa toda revisión de crecimiento: de dónde vinieron los clientes de este año y adónde se fueron los del año pasado.

Paso 1: retenidos y perdidos

Dos listas de compradores, una por año, y dos comparaciones:

• retained = compradores de 2024 INTERSECT compradores de 2025
• lapsed = compradores de 2024 EXCEPT compradores de 2025

Las CTE de compradores son una fila por pedido, así que cuenta DISTINCT customer_id cuando quieras el tamaño de una lista. Las operaciones de conjuntos ya devuelven ids distintos. En Harbor & Pine, 997 clientes compraron en 2024; 752 de ellos volvieron a comprar (75.4%) y 245 no. Retenidos más perdidos deben ser igual al total de 2024, una comprobación de sensatez gratis.

Paso 2: cuatro estados, una tabla

2025 tuvo 1,447 compradores. Dos de los grupos son fáciles, nuevos y retenidos. El tercero es el que la gente se pierde: clientes que compraron antes de 2024, se saltaron 2024 y volvieron en 2025. Llamarlos “nuevos” sobrestima la adquisición, así que reciben su propia etiqueta, reactivated:

SELECT customer_id FROM (
  SELECT customer_id FROM buyers_2025
  INTERSECT
  SELECT customer_id FROM buyers_before_2025
) AS b
EXCEPT
SELECT customer_id FROM buyers_2024

Envolver la primera operación en una subconsulta hace explícito el orden de las operaciones. (INTERSECT se asocia con más fuerza que UNION y EXCEPT en el SQL estándar, pero los paréntesis son más baratos que acordarse.)

Los grupos no se solapan, así que UNION ALL con una etiqueta de estado los apila, y un GROUP BY cuenta cada uno: 752 retenidos, 635 nuevos, 245 perdidos, 60 reactivados. Nuevos + retenidos + reactivados = 1,447, cada comprador de 2025 exactamente una vez.

Paso 3: cuánto gastó cada grupo

Ahora une el resultado de una operación de conjuntos con datos reales. Agrega primero los ingresos de 2025 a una fila por cliente y luego únelos con las etiquetas. Los clientes retenidos aportan cerca de $1.49 millones a unos $1,987 cada uno, los clientes nuevos unos $758,000 a unos $1,193, y los 60 clientes reactivados unos $50,000. Los clientes perdidos no tienen ingresos en 2025, así que el inner join los descarta, que es lo que pide la tarea.

Errores comunes

• Contar pedidos en lugar de clientes en el paso 1 (el starter devuelve 3,673, el número de pedidos completados de 2024).
• Juntar nuevos y reactivados, o dejar que un cliente caiga en dos grupos. Comprueba que los totales de los grupos sumen los conteos anuales de compradores.
• Unir las etiquetas con order_items y contar filas, lo que convierte clientes en líneas de pedido.

En SQL Server

INTERSECT, EXCEPT y UNION ALL funcionan igual, y las tablas derivadas necesitan un alias como aquí. Las subconsultas escalares del paso 1 están bien en T-SQL, pero 100.0 * importa más allí: con dos conteos enteros, / truncaría el porcentaje a 0.