SQL Interview Prep · Lección

Consultas de abandono y reactivación

Identificar a los usuarios que se fueron y a quienes regresaron después de un intervalo.

Consultas de abandono y reactivación es una lección gratuita de SQL Interview Prep en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Interview Prep incluye 4 lecciones en total.

La otra cara de la retención

Si la retención mide quién permaneció, el abandono mide quién se fue y la reactivación mide quién regresó. Los entrevistadores suelen tratar estos conceptos junto con la retención porque revelan si sabe razonar sobre la ausencia de actividad, lo que es más difícil que contar presencias.

El truco recurrente es que no puede filtrar filas que no existen. Las consultas de abandono consisten fundamentalmente en encontrar el intervalo sin actividad entre la última actividad de un usuario y el presente, o entre su actividad anterior y la siguiente.

Definir el abandono con precisión

«Abandonó» no significa nada sin una ventana temporal. Una definición habitual es que un usuario ha abandonado si no ha tenido ninguna actividad en los últimos 30 días. El umbral de 30 días de inactividad es una decisión de negocio que debe precisar.

En los productos de suscripción, el abandono puede significar en cambio una suscripción cancelada o vencida: un cambio de estado y no un intervalo sin actividad. Aclare qué modelo se aplica antes de escribir SQL.

Última actividad de cada usuario

La base del abandono por intervalo de actividad es el evento más reciente de cada usuario. Agrupe por usuario y obtenga MAX de la fecha del evento.

Este único valor, comparado con la fecha de hoy, indica cuánto tiempo lleva el usuario inactivo. Todo lo que sigue consiste en comparar con esta fecha de última actividad.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

La consulta de usuarios que abandonaron

Un usuario ha abandonado si su última actividad ocurrió hace más de 30 días. Compare last_active con CURRENT_DATE - 30. Cualquier usuario cuyo evento más reciente sea anterior a ese límite ha dejado de estar activo.

Observe que el trabajo se realiza después de la agregación: primero reduce los datos a una fila por usuario y después comprueba el intervalo. Filtrar los eventos sin procesar por fecha solo indicaría quién estuvo inactivo durante una ventana, no quién ha abandonado en general.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Calcular la tasa de abandono

La tasa de abandono es el número de usuarios que abandonaron dividido entre la base pertinente, que a menudo consiste en los usuarios activos al inicio del período. Use agregación condicional para contar los usuarios que abandonaron y el total en una sola pasada; después, divida con cuidado utilizando 100.0 y NULLIF.

Explique claramente el denominador en la entrevista: el abandono sobre todos los usuarios históricos y el abandono sobre los usuarios que habían estado activos anteriormente son métricas diferentes.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Abandono entre períodos con lógica de conjuntos

Otra forma de plantearlo es: ¿quién estuvo activo el mes pasado, pero no este mes? Se trata de una diferencia de conjuntos. Construya el conjunto de usuarios activos del mes pasado y el conjunto de usuarios activos de este mes; después, encuentre los miembros del primero que no estén en el segundo.

Puede expresarlo con EXCEPT, un anti-join mediante LEFT JOIN / IS NULL o NOT EXISTS. El anti-join es la opción más portable y la que los entrevistadores suelen querer ver.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

La forma de anti-join

La misma consulta de abandono de este periodo expresada como un anti-join: haga un LEFT JOIN de los usuarios activos de este mes con los del mes pasado y, después, conserve las filas cuyo resultado coincidente sea NULL. Son los usuarios que estaban presentes el mes pasado, pero ausentes este mes: los usuarios que abandonaron.

NOT EXISTS es una alternativa igual de válida y gestiona los NULL de forma segura. Conviene mencionar que NOT IN sería arriesgado si el conjunto interno pudiera contener NULL, un error clásico.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Definición de la reactivación

La reactivación (también llamada reactivation) corresponde a un usuario que había quedado inactivo y después volvió a estar activo. El indicador característico es un intervalo en su línea temporal: actividad, después un periodo de silencio superior al umbral de abandono y, finalmente, actividad otra vez.

Por tanto, un usuario reactivado este mes es alguien que está activo ahora, estuvo inactivo durante el periodo anterior, pero tuvo actividad en algún periodo más antiguo. Es la imagen especular del abandono.

Detección de intervalos con LAG

La forma elegante de encontrar reactivaciones es la función de ventana LAG: para cada periodo de actividad de cada usuario, consulte el periodo activo anterior. Si el intervalo entre ambos supera el umbral, este periodo corresponde a una reactivación.

LAG evita un self-join y resulta fácil de leer. Haga la partición por usuario, ordene por el periodo activo y compare cada periodo con su predecesor.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Nuevos, reactivados y retenidos

Una consulta completa de clasificación de actividad etiqueta a cada usuario activo durante este periodo como uno de los siguientes: nuevo (sin actividad anterior), retenido (también estuvo activo el periodo pasado) o reactivado (tuvo actividad anterior, pero existe un intervalo). El valor prev_month de LAG determina las tres categorías.

prev_month IS NULL → nuevo
prev_month = active_month - 1 → retenido
en cualquier otro caso (un intervalo) → reactivado

Producir este desglose es una respuesta sólida y completa.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

La trampa de NOT IN con NULL

Un último problema importante. Si expresa el abandono como WHERE user_id NOT IN (SELECT user_id FROM this_month) y esa subconsulta devuelve aunque sea un NULL, el resultado completo queda vacío, porque NOT IN se evalúa como UNKNOWN frente a NULL.

Prefiera NOT EXISTS o un anti-join con LEFT JOIN / IS NULL, que funcionan correctamente con NULL. Señalar esta diferencia sin que se lo pidan es una señal fiable de experiencia en entrevistas sobre retención.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Comprobación rápida

Quiere obtener los usuarios que estuvieron activos el mes pasado, pero no este mes. Un compañero escribió WHERE user_id NOT IN (SELECT user_id FROM this_month) y la consulta devuelve cero filas, aunque está claro que algunos usuarios abandonaron. ¿Cuál es la solución más segura?

Resumen: abandono y reactivación

Aspectos esenciales del abandono y la reactivación:

Defina el abandono mediante un umbral de inactividad (por ejemplo, 30 días sin actividad) o mediante un cambio en el estado de la suscripción; aclare cuál de los dos utiliza.
Calcule la MAX(última actividad) de cada usuario y compárela con CURRENT_DATE - threshold.
El abandono entre periodos es una diferencia de conjuntos: use EXCEPT, NOT EXISTS o un anti-join con LEFT JOIN / IS NULL.
La reactivación es un intervalo en la línea temporal; detecte ese intervalo con LAG para clasificar a los usuarios como nuevos, retenidos o reactivados.
Evite NOT IN cuando pueda haber NULL: vacía el resultado silenciosamente.

Preguntas frecuentes

¿La lección «Consultas de abandono y reactivación» es gratis?

Sí — el texto completo de «Consultas de abandono y reactivación» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Interview Prep, actualiza a CoddyKit PRO. El curso de SQL Interview Prep incluye 4 lecciones en total.

¿Qué aprenderé en «Consultas de abandono y reactivación»?

Identificar a los usuarios que se fueron y a quienes regresaron después de un intervalo. Practicas SQL Interview Prep con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Interview Prep?

No se requiere experiencia previa. SQL Interview Prep en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Consultas de abandono y reactivación»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Interview Prep?

Sí. Cada lección de SQL Interview Prep incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

Definir una cohorte por la primera acción
Crear una matriz de retención
Retención del día N y retención móvil
Consultas de abandono y reactivación

← Volver a SQL Interview Prep