0Pricing
Coding Interview Prep · Lección

Crear una matriz de retención

Contar usuarios activos por cohorte y desplazamiento de periodo para formar una tabla de retención.

Crear una matriz de retención es una lección gratuita de Coding Interview Prep en CoddyKit. Esta es la lección 2 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 Coding Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de Coding Interview Prep incluye 4 lecciones en total.

Qué es una matriz de retención

El paso siguiente a definir una cohorte es la famosa matriz de retención: las filas son cohortes, las columnas son desplazamientos de período (mes 0, 1, 2, ...) y cada celda cuenta cuántos usuarios de esa cohorte seguían activos en ese desplazamiento.

A los entrevistadores les gusta porque obliga a combinar la asignación de cohortes, una combinación con la actividad, un cálculo de la diferencia entre períodos y una tabla dinámica. Es la consulta más representativa de la analítica de producto.

Las dos entradas

Necesita dos elementos: el período de cohorte de cada usuario (de la lección anterior) y un registro de cada período de actividad de cada usuario. La actividad procede de la misma tabla de eventos, reducida a la granularidad del período.

Por tanto, planifique la consulta así: una CTE de cohortes, seguida de una CTE de actividad que enumere los meses en los que estuvo activo cada usuario, y después combínelas.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Enumerar los períodos activos

La CTE de actividad responde a la pregunta "¿en qué meses estuvo activo cada usuario?". Trunque cada evento al mes y elimine los duplicados con DISTINCT o GROUP BY, de modo que un usuario activo 40 veces en marzo produzca una sola fila de marzo.

Esta lista por usuario y por mes es la que debe combinar con la cohorte para medir la permanencia a lo largo de los distintos desplazamientos.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Calcular el desplazamiento del período

El núcleo de la matriz es el número de período: ¿cuántos meses después del inicio de la cohorte tuvo lugar una actividad determinada? Reste el mes de la cohorte del mes de actividad.

En Postgres, una forma clara consiste en contar los meses completos entre ambas fechas. Una fórmula portable multiplica la diferencia de años por 12 y suma la diferencia de meses; muchos motores también ofrecen funciones auxiliares. El desplazamiento 0 representa el propio mes de inicio de la cohorte.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Unir la cohorte con la actividad

Una la CTE de cohortes con la CTE de actividad mediante user_id. Cada fila del resultado indica: este usuario, perteneciente a la cohorte X, estuvo activo en el desplazamiento N. Contar los usuarios distintos por (cohorte, desplazamiento) constituye la matriz en formato largo.

Como cada miembro de una cohorte está activo en su propio mes inicial, el desplazamiento 0 debe ser igual al tamaño de la cohorte, lo que proporciona una comprobación de coherencia incorporada.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

La tabla de retención en formato largo

Añada el cálculo del desplazamiento y realice la agregación. Ahora tiene un resultado ordenado en formato largo: una fila por cohorte y desplazamiento, con un conteo de usuarios retenidos. Muchos entrevistadores aceptan directamente este resultado porque pivotar es solo una cuestión de presentación.

Observe que la expresión del desplazamiento aparece tanto en SELECT como en GROUP BY, ya que se calcula y no es una columna almacenada.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Convertir los desplazamientos en columnas anchas

Para obtener la cuadrícula clásica, convierta los desplazamientos en columnas mediante agregación condicional: una SUM de un CASE para cada desplazamiento. Este patrón portable funciona en cualquier dialecto sin una sintaxis especial de PIVOT.

Cada CASE emite 1 cuando el valor period_number de la fila coincide con el de esa columna, por lo que SUM cuenta los usuarios retenidos para ese desplazamiento.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

De los conteos a las tasas de retención

Por lo general, los entrevistadores quieren porcentajes, no conteos sin procesar. Divida los usuarios retenidos de cada desplazamiento entre el tamaño de la cohorte (desplazamiento 0). Convierta el valor a float o multiplíquelo por 1.0 para evitar la división entera, el error silencioso más común en este caso.

El resultado es una curva de retención: 100 % en el mes 0, que desciende hacia una meseta. Esa meseta es la métrica que realmente les interesa a las partes interesadas.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

La trampa de la división entera

Una trampa de entrevista prácticamente garantizada: en la mayoría de los motores, 120 / 500 es igual a 0, no a 0.24, porque ambos operandos son enteros. Los porcentajes de retención terminan siendo silenciosamente ceros en todos los casos.

Corríjalo haciendo que uno de los lados sea numérico: multiplique por 100.0, convierta un operando a NUMERIC mediante CAST o divida entre NULLIF(size, 0) para protegerse también contra una cohorte vacía. Decir «y NULLIF evita la división por cero» le dará puntos adicionales.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Rellenar los desplazamientos faltantes con cero

Si una cohorte no tuvo usuarios retenidos en el desplazamiento 2, el JOIN no produce ninguna fila y deja un hueco en la matriz. Para mostrar un 0 explícito, genere la cuadrícula completa de combinaciones (cohorte, desplazamiento) y haga un LEFT JOIN con los conteos.

Construya la cuadrícula mediante un CROSS JOIN de las cohortes con una lista de números o desplazamientos y aplique COALESCE a los conteos faltantes para convertirlos en cero. Los entrevistadores valoran que haya detectado este vacío.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Forma triangular y sesgo de recencia

Hay otro punto importante que mencionar: la matriz es triangular. Una cohorte que comenzó el mes pasado todavía no puede tener un valor para el mes 3, por lo que los desplazamientos posteriores incluyen a menos cohortes.

Por ello, comparar el promedio de una columna entre cohortes está sesgado a favor de las cohortes más antiguas. Mencione que mostraría el triángulo tal como es o que limitaría las comparaciones a los desplazamientos que todas las cohortes hayan alcanzado. Esta consciencia distingue a los analistas de quienes solo escriben consultas.

Comprobación rápida

Su consulta de retención divide los usuarios retenidos entre el tamaño de la cohorte, pero todos los porcentajes aparecen como 0, excepto el del mes 0. ¿Cuál es la causa más probable?

Resumen: la matriz de retención

Para construir una matriz de retención en una entrevista:

  • Asigne a cada usuario un período de cohorte y, después, enumere los períodos de actividad de cada usuario sin duplicados.
  • Únalos y calcule el desplazamiento del período (los meses entre la cohorte y la actividad).
  • Agregue en formato largo con COUNT(DISTINCT user_id); convierta mediante CASE si necesita una cuadrícula.
  • Convierta los conteos a tasas con cuidado, evitando la división entera y la división por cero mediante 100.0 y NULLIF.
  • Haga un LEFT JOIN con una cuadrícula generada para rellenar las celdas con cero y recuerde que la matriz es triangular.

Preguntas frecuentes

¿La lección «Crear una matriz de retención» es gratis?

Sí — el texto completo de «Crear una matriz de retenció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 Coding Interview Prep, actualiza a CoddyKit PRO. El curso de Coding Interview Prep incluye 4 lecciones en total.

¿Qué aprenderé en «Crear una matriz de retención»?

Contar usuarios activos por cohorte y desplazamiento de periodo para formar una tabla de retención. Practicas Coding 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 Coding Interview Prep?

No se requiere experiencia previa. Coding 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 2 de 4.

¿Cuánto tiempo toma la lección «Crear una matriz de retenció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 Coding Interview Prep?

Sí. Cada lección de Coding 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

  1. Definir una cohorte por la primera acción
  2. Crear una matriz de retención
  3. Retención del día N y retención móvil
  4. Consultas de abandono y reactivación
← Volver a Coding Interview Prep