Entrevista de SQL: Calcular un embudo de conversión ordenado en un plazo de 24 horas

Enunciado y contexto aplicable

Se le proporciona esta tabla de PostgreSQL:

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

Calcule un embudo de tres pasos: visit → signup → purchase. Un usuario entra en la cohorte si tiene al menos un visit en el intervalo de reporte semiabierto [start_at, end_at). Su visita más temprana en ese intervalo es el ancla del embudo. El registro calificado es el registro más temprano después de esa ancla; la compra calificada es la compra más temprana después del registro seleccionado. Ambos deben ocurrir a más tardar 24 horas después de la visita ancla.

event_id es único. El productor de eventos garantiza que cuando dos eventos para el mismo usuario comparten una marca de tiempo, el event_id menor ocurrió primero. Una compra exactamente 24 horas después del ancla cuenta; una incluso un microsegundo después no. Se permiten eventos entre los pasos del embudo, y los eventos de pasos duplicados no deben contar a un usuario dos veces.

Devuelva una fila con visited_users, signed_up_users, purchased_users, signup_rate y purchase_rate. Ambas tasas son acumuladas a partir de la cohorte de visitas, expresadas como fracciones redondeadas a cuatro decimales. Una cohorte vacía devuelve recuentos en cero y tasas nulas.

Esta pregunta se adapta a entrevistas de analista de datos, ingeniero de analítica y analítica de producto. Evalúa mucho más que la agregación condicional: el candidato debe definir el grano de la cohorte, preservar el orden de los eventos, seleccionar una cadena válida cuando los eventos se repiten, manejar los límites de tiempo y demostrar que la consulta no puede contar un recorrido imposible.

Lo que evalúa el entrevistador

La primera señal es la disciplina con el contrato de la métrica. "Usuarios que completaron los tres eventos" no es suficiente. El entrevistador quiere saber qué visita ancla el recorrido, si los pasos deben estar ordenados, si la ventana se cierra 24 horas después de la visita o después de cada paso, y si las tasas usan el paso anterior o la cohorte original como denominador. Cada elección cambia el resultado.

La segunda señal es el control del grano. La tabla sin procesar es una fila por evento, pero la salida cuenta usuarios. La cohorte debe tener a lo sumo una fila por usuario, y cada búsqueda posterior debe producir a lo sumo un evento para esa fila de la cohorte. Un join amplio seguido de COUNT(DISTINCT user_id) puede ocultar una cadena de eventos incorrecta en lugar de establecer una.

La tercera señal es el razonamiento secuencial. Expresiones independientes como MIN(CASE WHEN event_name = 'purchase' ...) no necesariamente encuentran la compra más temprana después del registro seleccionado. Un usuario puede comprar, luego registrarse y luego comprar de nuevo. La compra válida es la segunda. Por lo tanto, cada paso necesita el evento seleccionado por el paso anterior.

La cuarta señal es la lógica temporal determinista. Las marcas de tiempo por sí solas no ordenan eventos con el mismo tiempo. Este enunciado proporciona event_id como desempate, por lo que la clave de comparación es la tupla (event_time, event_id). Sin esa garantía, los datos no pueden demostrar qué evento concurrente ocurrió primero, y SQL no debería inventar la causalidad.

La señal final es el juicio operativo. Un embudo cuya ventana se extiende más allá de end_at necesita datos de eventos posteriores. El reporte madura solo después de que la canalización se haya procesado hasta end_at + 24 hours. El candidato debe analizar las llegadas tardías, los índices, los planes de consulta y los fixtures que ponen a prueba la definición de la métrica en lugar de detenerse en un SQL sintácticamente válido.

Preguntas para aclarar antes de responder

• ¿Qué evento ancla a un usuario? Esta respuesta utiliza la visita más temprana dentro del intervalo de reporte,

incluso si el usuario visitó antes del intervalo. La "primera visita histórica" requeriría datos históricos y un filtro de cohorte diferente.

• ¿El embudo está ordenado? Sí. Un registro debe seguir a la visita ancla, y una compra debe seguir al

registro seleccionado. La presencia de eventos no ordenados es una métrica diferente.

• ¿Dónde comienza y termina la ventana? Comienza en la visita ancla y se cierra de forma inclusiva en

visit_time + 24 hours. No se reinicia después del registro.

• ¿Pueden los pasos posteriores caer fuera de end_at? Sí, siempre que estén dentro de la ventana de 24 horas del usuario.

end_at selecciona las visitas ancla; no trunca las oportunidades de conversión.

• ¿Cómo se ordenan las marcas de tiempo iguales? Compare event_time primero y event_id segundo, utilizando la

garantía declarada por el productor. Si no existe una secuencia confiable, los pasos con el mismo tiempo son ambiguos.

• ¿Qué denominador define cada tasa? Ambos usan visited_users. Una tasa de compra paso a paso

dividiría los compradores entre los usuarios registrados y debería tener un nombre de columna diferente.

• ¿Qué devuelve una entrada vacía? Los recuentos son cero; las tasas son nulas porque no hay un denominador

significativo. NULLIF evita la división por cero.

• ¿Cuándo es definitivo el reporte? Solo después de que una marca de agua de integridad de datos cubra la

oportunidad completa de 24 horas de cada miembro de la cohorte y se haya aplicado la política de llegadas tardías.

Marco de respuesta de 30 segundos

“Primero haría que la cohorte sea de una fila por usuario clasificando las visitas en el intervalo de reporte semiabierto y conservando el rango uno. Para cada ancla, una búsqueda lateral izquierda encuentra el registro más temprano cuyo (event_time, event_id) sea posterior a la visita y cuyo tiempo esté dentro de las 24 horas. Una segunda búsqueda lateral izquierda hace lo mismo para la compra, pero comienza después del registro seleccionado mientras mantiene el plazo original de 24 horas. Eso crea una fila de progreso por usuario visitante. Luego puedo usar recuentos filtrados para las dos etapas alcanzadas y dividir ambos por el recuento de visitas. Probaría eventos invertidos, duplicados, marcas de tiempo iguales, el límite exacto de 24 horas, visitas repetidas, una cohorte vacía y la madurez del reporte.”

Análisis detallado paso a paso

La cohorte es lo primero. Filtrar antes de ROW_NUMBER() significa "visita más temprana en el intervalo de reporte", coincidiendo exactamente con el enunciado. Agregar event_id al orden de la ventana hace que el ancla seleccionada sea estable cuando las visitas comparten una marca de tiempo.

WITH ranked_visits AS (
  SELECT
    user_id,
    event_id AS visit_event_id,
    event_time AS visit_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_time, event_id
    ) AS visit_rank
  FROM events
  WHERE event_name = 'visit'
    AND event_time >= :start_at
    AND event_time < :end_at
),
cohort AS (
  SELECT user_id, visit_event_id, visit_time
  FROM ranked_visits
  WHERE visit_rank = 1
)
SELECT *
FROM cohort;

La consulta principal utiliza dos búsquedas laterales dependientes. PostgreSQL evalúa una subconsulta lateral con valores de las filas a su izquierda. LEFT JOIN LATERAL preserva la fila de la cohorte cuando no se encuentra ningún evento calificado, que es exactamente lo que necesita un embudo: un visitante que nunca se registra permanece en el denominador.

WITH ranked_visits AS (
  SELECT
    user_id,
    event_id AS visit_event_id,
    event_time AS visit_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_time, event_id
    ) AS visit_rank
  FROM events
  WHERE event_name = 'visit'
    AND event_time >= :start_at
    AND event_time < :end_at
),
cohort AS (
  SELECT user_id, visit_event_id, visit_time
  FROM ranked_visits
  WHERE visit_rank = 1
),
progress AS (
  SELECT
    c.user_id,
    c.visit_time,
    s.signup_time,
    p.purchase_time
  FROM cohort AS c
  LEFT JOIN LATERAL (
    SELECT
      e.event_id AS signup_event_id,
      e.event_time AS signup_time
    FROM events AS e
    WHERE e.user_id = c.user_id
      AND e.event_name = 'signup'
      AND (
        e.event_time > c.visit_time
        OR (
          e.event_time = c.visit_time
          AND e.event_id > c.visit_event_id
        )
      )
      AND e.event_time <= c.visit_time + INTERVAL '24 hours'
    ORDER BY e.event_time, e.event_id
    LIMIT 1
  ) AS s ON TRUE
  LEFT JOIN LATERAL (
    SELECT e.event_time AS purchase_time
    FROM events AS e
    WHERE e.user_id = c.user_id
      AND e.event_name = 'purchase'
      AND s.signup_time IS NOT NULL
      AND (
        e.event_time > s.signup_time
        OR (
          e.event_time = s.signup_time
          AND e.event_id > s.signup_event_id
        )
      )
      AND e.event_time <= c.visit_time + INTERVAL '24 hours'
    ORDER BY e.event_time, e.event_id
    LIMIT 1
  ) AS p ON TRUE
)
SELECT
  COUNT(*) AS visited_users,
  COUNT(*) FILTER (WHERE signup_time IS NOT NULL) AS signed_up_users,
  COUNT(*) FILTER (WHERE purchase_time IS NOT NULL) AS purchased_users,
  ROUND(
    COUNT(*) FILTER (WHERE signup_time IS NOT NULL)::numeric
      / NULLIF(COUNT(*), 0),
    4
  ) AS signup_rate,
  ROUND(
    COUNT(*) FILTER (WHERE purchase_time IS NOT NULL)::numeric
      / NULLIF(COUNT(*), 0),
    4
  ) AS purchase_rate
FROM progress;

La búsqueda de registros demuestra tres propiedades en conjunto: mismo usuario, orden estricto de tuplas después del ancla e inclusión en la ventana de 24 horas del ancla. El ordenamiento más LIMIT 1 selecciona un evento determinista. La búsqueda de compras depende de ese registro seleccionado, por lo que se ignora una compra anterior mientras que una compra válida posterior sigue siendo elegible. Su plazo límite todavía se refiere a c.visit_time; de lo contrario, la consulta concedería accidentalmente hasta 48 horas.

Esta elección voraz (greedy) es correcta para un embudo ordenado fijo de tres pasos. Elegir el registro calificado más temprano no puede eliminar una compra que un registro posterior podría admitir: cualquier compra posterior al registro tardío también es posterior al registro temprano, y el plazo límite común no cambia. El mismo argumento se aplica al elegir la compra calificada más temprana. El invariante después de cada búsqueda es que la cadena seleccionada es válida y deja el sufijo restante más grande posible de la ventana.

La CTE progress tiene exactamente una fila por usuario de la cohorte porque cada búsqueda lateral devuelve como máximo una fila. Por lo tanto, los agregados filtrados cuentan usuarios sin requerir DISTINCT. El recuento de compras no puede exceder el recuento de registros, y el recuento de registros no puede exceder el recuento de visitas. Esas desigualdades son aserciones útiles a nivel de resultados.

Evite el tentador patrón de mínimos independientes:

MIN(CASE WHEN event_name = 'signup' THEN event_time END),
MIN(CASE WHEN event_name = 'purchase' THEN event_time END)

Para la secuencia visit(09:00), purchase(09:05), signup(09:10), purchase(09:20), los mínimos independientes seleccionan la compra de las 09:05 y pueden rechazar al usuario. La consulta dependiente selecciona el registro de las 09:10 y luego la compra de las 09:20. Los mínimos condicionales pueden funcionar solo cuando cada mínimo está restringido por el paso seleccionado anterior, que es la dificultad central aquí.

Dos índices son puntos de partida plausibles:

CREATE INDEX events_anchor_scan_idx
  ON events (event_name, event_time, user_id, event_id);

CREATE INDEX events_user_step_lookup_idx
  ON events (user_id, event_name, event_time, event_id);

El primero respalda el nombre del evento y el rango de tiempo utilizados para construir la cohorte. El segundo respalda las búsquedas repetidas de pasos por usuario. Agregan almacenamiento y amplificación de escritura, y el valor real depende de la selectividad, la agrupación en clústeres, el tamaño de la tabla y el plan elegido por PostgreSQL. Valide con EXPLAIN (ANALYZE, BUFFERS) en datos representativos. Para reportes frecuentes a gran escala, un modelo de eventos con un campo de secuencia estable o una tabla de recorrido de usuario mantenida de forma incremental puede ser más apropiada, pero debe preservar el mismo contrato de cohorte y ventana.

Un fixture adversario compacto debería incluir estos casos:

1. purchase before visit -> visitor only
2. visit, purchase, signup -> signed up, not purchased
3. visit, purchase, signup, later purchase -> completes all steps
4. duplicate signups and purchases -> user still counts once per stage
5. purchase exactly at visit + 24 hours -> counts
6. purchase one microsecond after visit + 24 hours -> does not count
7. equal timestamps -> event_id determines sequence
8. repeated visits in the interval -> earliest visit remains the anchor
9. no visits -> zero counts and null rates

Pruebe también el ciclo de vida del reporte. Si end_at es la medianoche del 1 de julio, un visitante a las 23:59 del 30 de junio puede convertirse hasta las 23:59 del 1 de julio. Una ejecución a la medianoche del 1 de julio está incompleta. Utilice una marca de agua de ingesta, no el reloj de pared, y vuelva a calcular las cohortes afectadas cuando lleguen eventos tardíos.

Ejemplo de respuesta de alta calidad

“Declararía la regla de atribución antes de escribir SQL: una oportunidad de embudo por usuario, anclada en su visita más temprana dentro del intervalo de reporte. El intervalo es semiabierto, los pasos posteriores pueden ocurrir después de su finalización, y tanto el registro como la compra deben encajar dentro de las 24 horas posteriores al ancla. Las marcas de tiempo iguales se ordenan según la secuencia garantizada de ID de eventos.

Primero filtro las visitas al intervalo, las clasifico por (event_time, event_id) por usuario y conservo el rango uno. Eso proporciona el grano de denominador correcto. Para cada ancla uso una subconsulta lateral izquierda ordenada por la misma tupla para obtener el registro válido más temprano. Una segunda subconsulta lateral izquierda hace referencia al registro seleccionado y obtiene la compra válida más temprana, manteniendo la fecha límite vinculada a la visita. Dado que cada búsqueda tiene LIMIT 1, la relación de progreso sigue siendo de una fila por visitante. Los pasos faltantes permanecen nulos en lugar de eliminar al usuario.

El agregado final cuenta todas las filas de progreso, luego usa recuentos filtrados para marcas de tiempo de registro y compra no nulas. Ambas tasas se dividen por el recuento de la cohorte de visitas y usan NULLIF para entradas vacías. Yo afirmaría purchased_users <= signed_up_users <= visited_users, inspeccionaría cadenas de muestra para cada etapa y probaría específicamente eventos invertidos, pasos repetidos, marcas de tiempo iguales y ambos lados del límite de 24 horas.

Para producción, no consideraría completa la cohorte más nueva hasta que la marca de agua de ingesta cubra su ventana de oportunidad completa. Compararía planes con y sin índices que respalden el escaneo del ancla y las búsquedas de pasos por usuario. Si las marcas de tiempo y los ID de eventos no representan un orden confiable, me detendría y repararía el contrato de eventos porque la consulta no puede recuperar la causalidad faltante.”

Errores comunes

• Verificar solo la presencia del evento. Tres indicadores de eventos pueden contar purchase → visit → signup. Un embudo

necesita una cadena ordenada explícita.

• Tomar primeras marcas de tiempo independientes. La compra más temprana puede preceder al registro seleccionado incluso

cuando una compra posterior completa un recorrido válido.

• Anclar en cada visita involuntariamente. Eso cambia el grano de una oportunidad por usuario a una oportunidad

por visita y puede contar o atribuir a un usuario de manera diferente.

• Usar solo event_time para ordenar. Las marcas de tiempo iguales hacen que el resultado sea inestable. Utilice una clave de

secuencia confiable o reconozca que el orden no se puede conocer.

• Reiniciar la ventana en cada paso. Dar 24 horas al registro y otras 24 horas a la compra viola

un contrato de 24 horas basado en el ancla.

• Filtrar pasos posteriores por end_at. Eso acorta la oportunidad para los visitantes cercanos al límite

del reporte. Obtenga eventos posteriores a través de la fecha límite de cada ancla.

• Usar un inner lateral join. Los visitantes sin registro desaparecen, inflando la tasa de conversión.
• Contar filas de eventos sin procesar. Los reintentos y las acciones repetidas pueden hacer que los recuentos de etapas superen el tamaño de la cohorte.
• Dividir por el paso anterior accidentalmente. Eso calcula una tasa de conversión por pasos, no la tasa

acumulada indicada a partir de las visitas.

• Publicar una cohorte inmadura. La ausencia de un evento posterior no es evidencia de abandono hasta que la

ventana completa esté cubierta por datos confiables.

Preguntas de seguimiento y respuestas

¿Qué pasa si cualquier visita puede iniciar un recorrido válido?

El grano cambia. Genere anclas candidatas para cada visita, haga coincidir una cadena válida de cada una y luego aplique una regla de atribución como el recorrido completado más temprano por usuario. No se limite a reemplazar la CTE de anclas: las ventanas superpuestas pueden competir por el mismo evento posterior, por lo que el contrato del producto debe indicar si se permite la reutilización.

¿Qué pasa si cada paso debe compartir una sesión o un producto?

Lleve la clave de correlación desde el ancla y exíjala en ambas búsquedas laterales. El grano se convierte en usuario-sesión o usuario-producto en lugar de usuario solo. Defina cómo se comportan las claves nulas y si la clave es inmutable antes de confiar en el join.

¿Cómo admitiría diez pasos de embudo?

Diez joins laterales escritos a mano son difíciles de auditar. Dependiendo de la base de datos, use SQL recursivo, una función nativa de secuencia o embudo, o un trabajo de preprocesamiento que recorra los eventos ordenados como una máquina de estados. Conserve los mismos invariantes: un ancla definida, orden monótono de pasos, una fecha límite compartida y una regla de atribución auditable.

¿Qué pasa si los eventos llegan tarde o desordenados?

Separe el tiempo del evento del tiempo de ingesta, publique una marca de agua de completitud y reexprese las cohortes cuyas ventanas reciban datos tardíos. El orden de tiempo de los eventos aún se puede calcular después de la llegada, pero el reporte es provisional hasta que haya pasado el intervalo de retraso acordado.

¿Puede una función de ventana resolver todo el problema?

Puede hacerlo, por ejemplo escaneando el flujo ordenado de cada usuario y transportando el estado, pero LAG por sí solo no es suficiente porque pueden existir eventos irrelevantes y duplicados entre los pasos. La solución lateral hace explícita la dependencia entre los pasos seleccionados. Elija otra formulación solo si sus transiciones de estado y su semántica de atribución son igualmente claras y su plan medido es mejor.