Entrevista de SQL: Calcular la retención exacta de cohortes al Día 7

Enunciado y contexto aplicable

Se le proporcionan dos tablas de PostgreSQL:

CREATE TABLE users (
  user_id bigint PRIMARY KEY,
  signup_at timestamptz NOT NULL,
  acquisition_channel text
);

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  event_at timestamptz NOT NULL,
  event_name text NOT NULL
);

Para los usuarios cuya fecha de registro se encuentre entre el 1 de enero y el 31 de enero de 2026, reporte la retención exacta al Día 7 agrupada por fecha de registro y canal de adquisición. La zona horaria del negocio es America/New_York. El Día 0 es la fecha local de registro del usuario. Un usuario se considera retenido en el Día 7 si tiene al menos un evento core_action_completed en la fecha del calendario local signup_day + 7. Los eventos en los Días 1–6, Día 8 o cualquier fecha posterior no satisfacen esta definición.

Devuelva cohort_day, acquisition_channel, cohort_size, retained_users y d7_retention_rate. Múltiples eventos calificados cuentan una sola vez por usuario. Los usuarios sin evento de retorno deben permanecer en el denominador. Los canales nulos se agrupan como unknown.

Asuma data_complete_through = '2026-02-01 05:00:00+00'. Este es un watermark de ingesta exclusivo y equivale a la medianoche al inicio del 1 de febrero en Nueva York. Incluya únicamente una cohorte de registro cuando toda la fecha de su Día 7 en el calendario sea anterior a ese watermark. La cohorte del 24 de enero está madura porque su Día 7 es el 31 de enero; la cohorte del 25 de enero no lo está.

Este problema es útil en entrevistas de analytics engineer, data analyst y product analytics porque la parte difícil es el contrato de la métrica, no la división. La consulta debe alinear la granularidad de la cohorte, la ventana de retorno, la zona horaria, la completitud de los datos y la segmentación antes de agregar cualquier elemento.

Qué evalúa el entrevistador

La primera señal es si el candidato pregunta qué significa “retención al Día 7”. Tres métricas que suenan similares son materialmente diferentes: actividad exactamente en el Día 7, actividad en cualquier momento durante los Días 1–7 y actividad en el Día 7 o posterior. Una consulta puede ser sintácticamente perfecta y aun así responder a la pregunta equivocada. Este enunciado requiere la primera definición.

La segunda señal es la disciplina con el denominador. El denominador es cada usuario elegible en una cohorte de registro madura. Comenzar desde la tabla de eventos, o hacer un inner join de los retornos con los registros, elimina silenciosamente a los usuarios que nunca regresaron y sobreestima la retención. La cohorte debe construirse primero y preservarse con un left join.

La tercera señal es el control de la granularidad. La salida tiene una fila por fecha de registro y canal, mientras que el flag de retención tiene como máximo una fila por usuario. Los datos de eventos sin procesar pueden contener reintentos, duplicados y muchas acciones válidas en la misma fecha. Contar filas de eventos mediría el volumen de acciones en lugar de los usuarios retenidos, por lo que el numerador debe deduplicarse a nivel de usuario.

La cuarta señal es la corrección temporal. Un día de calendario local no siempre es un intervalo fijo de 24 horas y una zona con nombre puede cambiar su desplazamiento UTC bajo las reglas del horario de verano. Derivar primero una fecha local y construir los límites del día objetivo de cada usuario a partir de las medianoches de la zona con nombre expresa el contrato directamente. Una prueba como event_at = signup_at + INTERVAL '7 days' responde, en cambio, a una pregunta de tiempo transcurrido.

La señal final es el criterio operativo. Las cohortes recientes necesitan una ventana de observación completa, y un reloj de pared no demuestra que un pipeline de eventos esté completo. Un candidato debe utilizar un watermark de datos, explicar los eventos que llegan tarde, validar la granularidad de reporte más pequeña y confiable, y elegir índices o una tabla de actividad diaria de acuerdo con la frecuencia de consulta y el volumen de datos.

Preguntas para aclarar antes de responder

• ¿El Día 7 es exacto, acotado o móvil? Esta respuesta utiliza un evento exactamente en la séptima fecha local después del registro. “Dentro de los siete días” y “en o después del Día 7” requieren predicados diferentes.
• ¿Qué evento demuestra la retención? El enunciado utiliza core_action_completed. Login, page view, purchase o cualquier evento producirían un significado de producto diferente y no deben sustituirse a la ligera.
• ¿Qué define un día? El contrato de reporte utiliza fechas calendario de America/New_York. Zonas horarias por usuario o UTC cambiarían la pertenencia a la cohorte y las ventanas de retorno.
• ¿Cuándo madura una cohorte? Una cohorte es reportable solo cuando el watermark de datos exclusivo está en o más allá de la siguiente medianoche local después del Día 7 de esa cohorte.
• ¿Cómo se manejan los eventos tardíos? Si el watermark avanza posteriormente o llegan eventos retroactivos (backfilled) antes de él, las cohortes afectadas deben recalcularse. La tasa mostrada no debe tratarse como inmutable.
• ¿Qué valor de canal se aplica? La consulta asume que users.acquisition_channel es la atribución inmutable al momento del registro. Un canal actual mutable necesita en su lugar un snapshot de atribución versionado.
• ¿Cuál es la granularidad de la cohorte? Este enunciado agrupa por fecha de registro y canal. Las cohortes semanales utilizan la misma lógica a nivel de usuario pero una clave de agrupación final diferente.
• ¿La tasa debe ser una fracción o un porcentaje? La consulta devuelve una fracción redondeada a cuatro decimales: 0.3333 significa 33.33%.

Estructura de respuesta de 30 segundos

“Definiría el Día 7 exacto como la fecha del calendario de registro del usuario más siete en la zona horaria comercial acordada. Primero construyo la cohorte de enero a partir de los límites UTC de medianoche local y almaceno una fila por usuario con el día de registro y el canal al momento del registro. Luego excluyo las cohortes cuyo día objetivo completo esté más allá del watermark de datos exclusivo. Para cada usuario restante, busco el evento objetivo en el intervalo semiabierto del Día 7 local, deduplico a una fila retenida por usuario y uno mediante left join ese flag a la cohorte madura. Finalmente, cuento los usuarios de la cohorte y los usuarios retenidos por fecha y canal. Probaría duplicados, retornos cero, eventos del Día 6 y Día 8, medianoches locales, límites de horario de verano y el corte de madurez”.

Análisis detallado paso a paso

Comience con parámetros que hagan visible el contrato de reporte. cohort_end es exclusivo, y el watermark es el primer instante no observado. Los intervalos semiabiertos evitan contar dos veces un evento a medianoche y se componen limpiamente a través de fechas adyacentes.

WITH params AS (
  SELECT
    'America/New_York'::text AS tz,
    DATE '2026-01-01' AS cohort_start,
    DATE '2026-02-01' AS cohort_end,
    TIMESTAMPTZ '2026-02-01 05:00:00+00' AS data_complete_through
),
cohort AS (
  SELECT
    u.user_id,
    COALESCE(u.acquisition_channel, 'unknown') AS acquisition_channel,
    (u.signup_at AT TIME ZONE p.tz)::date AS signup_day
  FROM users AS u
  CROSS JOIN params AS p
  WHERE u.signup_at >= (p.cohort_start::timestamp AT TIME ZONE p.tz)
    AND u.signup_at < (p.cohort_end::timestamp AT TIME ZONE p.tz)
),
mature_cohort AS (
  SELECT c.*
  FROM cohort AS c
  CROSS JOIN params AS p
  WHERE c.signup_day + 7
    < (p.data_complete_through AT TIME ZONE p.tz)::date
),
retained AS (
  SELECT DISTINCT c.user_id
  FROM mature_cohort AS c
  CROSS JOIN params AS p
  JOIN events AS e
    ON e.user_id = c.user_id
   AND e.event_name = 'core_action_completed'
   AND e.event_at >= ((c.signup_day + 7)::timestamp AT TIME ZONE p.tz)
   AND e.event_at < ((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
)
SELECT
  c.signup_day AS cohort_day,
  c.acquisition_channel,
  COUNT(*) AS cohort_size,
  COUNT(r.user_id) AS retained_users,
  ROUND(
    COUNT(r.user_id)::numeric / NULLIF(COUNT(*), 0),
    4
  ) AS d7_retention_rate
FROM mature_cohort AS c
LEFT JOIN retained AS r
  ON r.user_id = c.user_id
GROUP BY c.signup_day, c.acquisition_channel
ORDER BY c.signup_day, c.acquisition_channel;

El filtro de cohorte convierte los límites de medianoche local de enero a instantes UTC antes de compararlos con los valores indexados de timestamptz. Esto es preferible a aplicar una conversión de fecha a cada fila de signup_at en la cláusula WHERE: establece la regla de fecha local mientras mantiene la columna de timestamp elegible para un escaneo de rango normal.

La madurez es más fácil de razonar en fechas. El watermark se convierte a la fecha local 1 de febrero. Una fecha objetivo debe ser estrictamente anterior al 1 de febrero, lo que significa que su medianoche de cierre está cubierta. El 24 de enero más siete es 31 de enero y califica. El 25 de enero más siete es 1 de febrero y no califica. Si el watermark fuera al mediodía en lugar de una medianoche local, la forma robusta compararía el instante final del día objetivo directamente con el watermark:

((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
  <= p.data_complete_through

La CTE de retenidos crea un flag de usuario similar a un semi-join. DISTINCT hace que cada usuario contribuya como máximo con una fila, incluso si el productor del evento reintentó o el usuario completó la acción principal diez veces. Debido a que un usuario pertenece exactamente a una fila de cohorte, unir por user_id es suficiente bajo el esquema establecido. Si la misma persona pudiera tener múltiples episodios de registro, el modelo necesitaría un identificador de episodio estable y el join lo usaría.

El left join final conserva a todos los miembros de la cohorte madura. Por lo tanto, COUNT(*) mide el denominador, mientras que COUNT(r.user_id) cuenta solo los flags retenidos que coincidieron. NULLIF es defensivo a nivel de grupo, aunque un grupo producido a partir de mature_cohort necesariamente contiene al menos una fila.

Para un caso de prueba pequeño, suponga que la cohorte orgánica del 1 de enero tiene tres usuarios y solo uno tiene una acción válida en el Día 7. Su resultado es 3, 1 y 0.3333. Si la cohorte paga del 2 de enero tiene dos usuarios y ambos regresan, su resultado es 2, 2 y 1.0000. Un registro del 25 de enero está ausente independientemente de eventos posteriores porque esa cohorte es inmadura con el watermark suministrado.

No promedie las tasas de grupo mostradas para crear un total. Una cohorte diaria de un usuario no debe tener el mismo peso que una cohorte de mil. El rollup correcto es:

overall_retention = SUM(retained_users) / SUM(cohort_size)

En datos con tamaño de producción, los índices útiles coinciden con los límites selectivos y las claves de unión:

CREATE INDEX users_signup_at_idx
  ON users (signup_at);

CREATE INDEX events_core_action_user_time_idx
  ON events (user_id, event_at)
  WHERE event_name = 'core_action_completed';

El índice parcial de eventos es apropiado solo cuando esta definición de evento es estable e importante como para justificar su costo de escritura y almacenamiento. Para un dashboard recurrente sobre un flujo de eventos muy grande, una tabla mantenida incrementalmente con una clave única como (user_id, activity_day, event_name) puede eliminar los escaneos repetidos de eventos sin procesar. Su activity_day debe derivarse bajo el mismo contrato de zona con nombre; de lo contrario, la optimización cambia la métrica.

Sea U los usuarios elegibles y E las filas de eventos relevantes examinadas a través de la unión. El escaneo de cohortes es lineal respecto a los usuarios seleccionados, la deduplicación suele ser un hash o sort sobre los candidatos retenidos, y la agregación final es lineal respecto a los usuarios maduros. El costo real depende de la selectividad de eventos, índices, estadísticas y la forma del plan, así que inspeccione EXPLAIN (ANALYZE, BUFFERS) sobre datos representativos en lugar de prometer una complejidad universal basándose solo en el texto SQL.

Respuesta de muestra de alta calidad

“Antes de escribir SQL, fijaría cuatro definiciones: Día 7 exacto, core_action_completed como el evento de retorno, fechas calendario de Nueva York y un watermark de completitud exclusivo. El denominador es cada registro de enero cuya fecha de Día 7 completa haya sido observada; el numerador son usuarios distintos de ese conjunto con al menos un evento calificado.

Construiría la cohorte primero. Convierto las medianoches locales de inicio y fin de enero a instantes UTC para el filtro de rango signup_at, luego retengo la fecha de registro local de cada usuario y el canal al momento del registro. Filtro la madurez por separado para que pueda ser auditada: con un watermark de medianoche local al 1 de febrero, el 24 de enero es la última fecha de registro incluida.

Para cada usuario maduro, uno eventos por user_id, el nombre del evento objetivo y el intervalo semiabierto desde la medianoche local en signup_day + 7 hasta la medianoche local en signup_day + 8. Esos límites se calculan en la zona horaria con nombre, por lo que una transición de horario de verano no convierte una regla de calendario en una regla de 168 horas. Selecciono IDs de usuario distintos, uno mediante left join los flags de retención a la cohorte y agrego por fecha de registro y canal. Ese left join es fundamental porque los usuarios sin retorno aún pertenecen al denominador.

Validaría las CTE de forma independiente. La CTE de cohorte debe tener una fila por registro de enero; la CTE de madurez debe detenerse en el 24 de enero para este watermark; la CTE de retenidos debe tener usuarios únicos; y los conteos finales deben satisfacer 0 <= retained_users <= cohort_size. Los casos de prueba cubrirían eventos duplicados, sin retorno, nombres de eventos incorrectos, Día 6 y Día 8, ambos lados de la medianoche local, una fecha con cambio de horario de verano, canales nulos y una carga retroactiva de eventos tardíos. Para un total entre grupos, dividiría la suma de usuarios retenidos entre la suma de usuarios de la cohorte en lugar de promediar las tasas”.

Errores comunes

• Usar un inner join. Esto descarta a los usuarios sin retorno e infla la tasa. Construya la cohorte primero y haga left join con un flag de retención a nivel de usuario.
• Contar eventos en lugar de usuarios. COUNT(e.event_id) puede exceder el tamaño de la cohorte. Deduplique el numerador por usuario o use una comprobación de existencia booleana.
• Dejar el Día 7 ambiguo. Un predicado que cubra los Días 1–7 o del Día 7 en adelante calcula una métrica diferente. Escriba la definición del intervalo en palabras antes del SQL.
• Comparar contra el timestamp de registro más 168 horas. Esa es la retención por tiempo transcurrido, no una definición de fecha de calendario en una zona horaria con nombre, y puede divergir en torno a los cambios de horario de verano.
• Hacer un cast de timestamps indexados dentro del filtro. Convertir cada signup_at a una fecha puede impedir un escaneo de rango útil. Convierta en su lugar los límites locales constantes a instantes.
• Incluir cohortes inmaduras. Los usuarios recientes no han tenido la oportunidad completa de regresar, creando un bajo rendimiento artificial. Condicione según el watermark del pipeline, no simplemente la hora actual.
• Tratar un watermark como una verdad permanente. Las cargas retroactivas pueden cambiar cohortes ya reportadas. Defina el comportamiento de recálculo y frescura.
• Leer un valor de canal mutable. La atribución actual puede filtrar información futura en cohortes históricas. Use un campo de registro inmutable o un snapshot versionado.
• Promediar porcentajes de grupos. Los promedios no ponderados distorsionan los totales cuando los tamaños de cohorte difieren. Sume primero los conteos.
• Ignorar la semántica de valores vacíos y nulos. Mapee los canales nulos deliberadamente y decida si la falta de datos de eventos significa cero actividad o un pipeline incompleto antes de publicar la métrica.

Preguntas de seguimiento y respuestas

¿Cómo calcularía la retención dentro de los Días 1–7?

Mantenga el mismo enfoque de cohorte y madurez, pero cambie el intervalo del evento para que comience en la medianoche local de signup_day + 1 y termine antes de la medianoche local de signup_day + 8. La madurez aún requiere que todo el séptimo día esté completo. Especifique si el Día 0 debe contar; las herramientas y equipos de producto difieren en esto.

¿Cómo calcularía la retención de “Día 7 o posterior”?

El límite inferior sigue siendo la medianoche local en signup_day + 7, pero el límite superior se convierte en el corte de reporte. Esa métrica es acumulativa y depende de la duración de la observación: una cohorte más antigua tiene más posibilidades de regresar. Compare cohortes solo a una edad común o publique una curva de retención en lugar de un único número sin límite.

¿Puede PostgreSQL agregar esto sin una CTE de retenidos separada?

Sí. Una búsqueda lateral con EXISTS o una agregación cuidadosamente construida puede devolver un booleano por usuario de la cohorte. Un join directo más COUNT(DISTINCT e.user_id) FILTER (WHERE ...) también es posible, pero puede materializar muchas filas de eventos antes de la agregación. La CTE separada facilita la auditoría de la granularidad y la prueba de corrección; elija el plan final a partir de datos medidos.

¿Qué sucede si cada usuario tiene una zona horaria de reporte diferente?

Almacene la zona que se aplica al episodio de registro y derive tanto el día de la cohorte como los límites objetivo a partir de ese mismo valor. Los cambios históricos de zona necesitan una política establecida. Las zonas por usuario también significan que la etiqueta de una cohorte de calendario abarca diferentes intervalos UTC, por lo que las preagregaciones deben conservar la zona aplicable o el día local ya normalizado.

¿Cómo probaría el límite de madurez?

Cree registros el 24 y el 25 de enero bajo el watermark proporcionado. Otorgue a ambos un evento válido en el día objetivo. El 24 de enero debe aparecer y el 25 de enero no. Pruebe también un watermark un segundo antes y exactamente a la medianoche de cierre del día objetivo para confirmar la regla del límite exclusivo.

¿Cómo deben manejarse los eventos que llegan tarde?

Publique el watermark de tiempo de evento y recalcule todas las cohortes cuyos intervalos de eventos elegibles se superpongan con una carga retroactiva. Si el retraso de ingesta tiene un nivel de servicio conocido, los reportes pueden agregar un margen de seguridad más allá del cierre nominal del Día 7. Conserve el conteo sin procesar y la versión de la métrica para que una corrección sea trazable.

¿Qué invariantes monitorearía en producción?

Verifique que cada grupo tenga un cohort_size positivo, que los usuarios retenidos se mantengan entre cero y el tamaño de la cohorte, que no haya ninguna fecha de cohorte inmadura presente, que los totales de la cohorte coincidan con la fuente de registros y que la hora máxima de evento cubra el watermark declarado. Genere alertas por separado sobre anomalías en el volumen de eventos o en el retraso de ingesta para que una brecha en el pipeline no se interprete erróneamente como una caída en la retención.