0Pricing
SQL Interview Prep · Lección

Definir una cohorte por la primera acción

Asignar a cada usuario una cohorte según la fecha de su primer evento.

Definir una cohorte por la primera acción es una lección gratuita de SQL Interview Prep en CoddyKit. Esta es la lección 1 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.

Por qué las cohortes aparecen en las entrevistas

Cuando un entrevistador de analítica de producto dice "cree una cohorte", está comprobando si puede asignar cada usuario a un grupo según cuándo hizo algo por primera vez y después hacer un seguimiento de ese grupo a lo largo del tiempo.

Una cohorte es un conjunto de usuarios que comparte un evento inicial en el mismo período, normalmente su primera compra, registro o inicio de sesión. La utilidad de las cohortes es que permiten comparar a los usuarios en igualdad de condiciones: todos los usuarios de la cohorte de enero se miden desde su propio inicio en enero.

La primera habilidad, y la que se practica en esta lección, es calcular de forma fiable la fecha de la primera acción de cada usuario.

La tabla de origen

Casi todas las preguntas sobre cohortes parten de una tabla de eventos: una fila por cada acción del usuario, con una marca de tiempo. Imagine una tabla events:

  • user_id — quién realizó la acción
  • event_type — qué hizo
  • event_at — cuándo ocurrió, como marca de tiempo

En una entrevista, aclare explícitamente la granularidad: "¿Hay una fila por evento y un usuario puede aparecer varias veces?" La respuesta casi siempre es afirmativa, precisamente por eso necesita una agregación que reduzca los datos a la primera acción de cada usuario.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

Primera acción = MIN de la marca de tiempo

La operación central es sencilla: agrupe por user_id y obtenga MIN(event_at). Ese mínimo es la primera acción del usuario, el momento que lo incorpora a una cohorte.

Esta es la respuesta que los entrevistadores esperan oír primero, antes de recurrir a funciones de ventana más elaboradas. Un GROUP BY sencillo es correcto, legible y rápido.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Filtrar por un evento definitorio

A menudo, la cohorte se define por una acción específica, no por cualquier evento. "Agrupar a los usuarios por su primera compra" significa que debe filtrar las filas de compras antes de obtener el mínimo.

Coloque el filtro en WHERE para que MIN solo examine las filas que cumplen los requisitos. Un error frecuente en las entrevistas consiste en obtener MIN sobre todos los eventos y filtrar después, lo que asignaría una fecha de inicio incorrecta a cualquiera que navegara antes de comprar.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Agrupar en un período de cohorte

Una cohorte suele ser un período, no una marca de tiempo exacta: la "cohorte de 2024-03" o la "semana del 2024-03-04". Trunque la fecha de la primera acción al inicio de la granularidad del período.

En Postgres, use DATE_TRUNC('month', ...). En MySQL, puede usar DATE_FORMAT(d, '%Y-%m-01'); en SQL Server, DATETRUNC(month, d) o un primer día de mes calculado. Indique el dialecto que utiliza en la entrevista para que la elección de sintaxis parezca deliberada.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Envolverlo en una CTE

La asignación de cohorte por usuario es un bloque de construcción que reutilizará en las consultas de retención, así que inclúyala en una CTE con un nombre claro. Esto mantiene legibles los pasos siguientes y demuestra al entrevistador que piensa en piezas componibles.

A partir de aquí, cualquier consulta posterior puede combinarse con user_cohort para saber a qué grupo pertenece cada usuario.

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

Tamaño de la cohorte: contar sus miembros

La primera comprobación de coherencia que espera un entrevistador es el tamaño de la cohorte: cuántos usuarios pertenecen a cada cohorte. Agrupe la CTE de asignación por cohort_month y cuente los usuarios distintos.

Use COUNT(DISTINCT user_id) como medida preventiva aunque la CTE ya tenga una fila por usuario; esto indica que está teniendo en cuenta la granularidad. Este recuento se convierte en el denominador de todos los porcentajes de retención posteriores.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Alternativa con una función de ventana

A veces los entrevistadores solicitan que la etiqueta de cohorte esté asociada a cada fila de evento, no una tabla resumida. Aquí resulta útil una función de ventana: MIN(event_at) OVER (PARTITION BY user_id) calcula la primera acción sin eliminar filas.

Esto resulta práctico cuando necesita tanto los eventos detallados como la etiqueta de cohorte en una sola pasada, que es la base para contar la retención.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

El problema de los empates y los duplicados

¿Qué ocurre si un usuario tiene dos eventos exactamente en la misma marca de tiempo más temprana? MIN lo gestiona correctamente: devuelve ese único valor mínimo independientemente de cuántas filas empaten, por lo que la asignación de cohorte sigue siendo una por usuario.

Compare esto con un enfoque basado en ROW_NUMBER() ... ORDER BY event_at, en el que los empates se resuelven arbitrariamente y debe añadir un criterio de desempate determinista (como event_id) para obtener un resultado estable. Mencionar esta diferencia sin que se lo pidan demuestra experiencia.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Zonas horarias y el límite del día

Una pregunta sutil en las entrevistas: una compra a las 23:30 en Nueva York corresponde al día siguiente en UTC. Si las cohortes se agrupan por día natural, la zona horaria determina a qué cohorte se incorpora el usuario.

La respuesta segura es almacenar las marcas de tiempo en UTC y convertirlas a la zona horaria del negocio antes de truncarlas. Indique explícitamente qué zona define el "día" de la métrica, porque esa única decisión puede cambiar de cohorte a miles de usuarios.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Excluir usuarios anteriores al período

Los análisis reales limitan la cohorte a un intervalo de fechas, por ejemplo, "cohortes que comenzaron en el primer trimestre". Filtre la fecha de la primera acción agregada, lo que significa usar una cláusula HAVING o un filtro externo sobre la CTE, no un WHERE sobre los eventos sin agregar.

Filtrar los eventos sin agregar por fecha permitiría incorrectamente que un usuario que compró por primera vez en diciembre, pero también tuvo actividad en el primer trimestre, entrara en una cohorte del primer trimestre. Aplique siempre el filtro a la primera acción calculada.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Comprobación rápida

Un entrevistador pregunta: "Agrupe a cada usuario por su mes de primera compra. Es posible que los usuarios naveguen antes de comprar". ¿Qué enfoque es correcto?

Resumen: definir una cohorte

Conclusiones clave para la pregunta de entrevista sobre la definición de cohortes:

  • Una cohorte agrupa a los usuarios por su primera acción que cumple los requisitos.
  • Calcúlela con MIN(event_at) después de filtrar el evento definitorio en WHERE.
  • Agrúpela en un período con DATE_TRUNC (o su equivalente en el dialecto correspondiente).
  • Incluya la asignación en una CTE para reutilizarla; COUNT(DISTINCT user_id) proporciona el tamaño de la cohorte.
  • Preste atención al límite diario de la zona horaria y limite los intervalos de fechas según la primera acción calculada, nunca según los eventos sin agregar.

Si domina esto, la matriz de retención de la siguiente lección se reduce a una combinación.

Preguntas frecuentes

¿La lección «Definir una cohorte por la primera acción» es gratis?

Sí — el texto completo de «Definir una cohorte por la primera acció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 «Definir una cohorte por la primera acción»?

Asignar a cada usuario una cohorte según la fecha de su primer evento. 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 1 de 4.

¿Cuánto tiempo toma la lección «Definir una cohorte por la primera acció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

  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 SQL Interview Prep