Entrevista de SQL: Calcular usuarios activos móviles de 7 días

Prompt y contexto aplicable

Una tabla analítica contiene events(event_id, user_id, event_at, event_name, is_internal_user). event_at es un timestamptz de PostgreSQL. Un usuario se considera activo tras al menos un evento app_open, view_dashboard o run_report. Los usuarios internos no cuentan.

Devuelve una fila por cada fecha del calendario en America/New_York desde el 1 de junio hasta el 30 de junio de 2026. Para cada fecha de reporte, rolling_7d_active_users es el número de usuarios elegibles distintos activos en esa fecha o en cualquiera de las seis fechas anteriores del calendario. Por lo tanto, el 1 de junio necesita la actividad desde el 26 de mayo hasta el 1 de junio, ambos inclusive. Un usuario con veinte eventos en tres fechas solo cuenta una vez en esa ventana.

La consulta debe conservar las fechas con cero usuarios, usar límites de tiempo explícitos y especificar cómo los eventos que llegan tarde afectan un resultado publicado previamente. El desafío central es una unión distinct móvil. No es una suma móvil de recuentos diarios previamente agregados.

Qué evalúa el entrevistador

La primera señal es la definición de la métrica antes de la sintaxis. Un candidato sólido establece los eventos elegibles, la población excluida, la zona horaria de reporte, el nivel de granularidad de salida, la ventana inclusiva de siete fechas y el límite de completitud de datos. Sin estos elementos, dos consultas sintácticamente válidas pueden responder preguntas diferentes.

La segunda señal es el control de la granularidad. Los eventos crudos deben transformarse primero en pares únicos (user_id, activity_date). Eso elimina los duplicados en el mismo día, pero retiene intencionalmente a un usuario en varias fechas. La ventana final luego cuenta la unión distinct de esos conjuntos de usuarios.

La tercera señal es rechazar atajos tentadores. Sumar siete valores de DAU cuenta a un usuario una vez por cada fecha activa. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW describe siete filas, no necesariamente siete fechas de calendario, y aplicarlo a recuentos diarios todavía no puede reconstruir la unión distinct entre días.

La señal final es el criterio de producción: escanear el periodo de preparación (warm-up) de seis días antes del rango solicitado, preservar fechas vacías con un calendar spine, convertir marcas de tiempo utilizando la zona horaria declarada, definir la semántica de actualización para eventos tardíos y elegir deliberadamente una estrategia de escalamiento exacta o aproximada.

Preguntas para aclarar antes de responder

• ¿Qué califica como activo? Una definición basada solo en inicio de sesión produce un conjunto diferente al de eventos

significativos del producto. Los nombres de eventos y las exclusiones de bots o usuarios internos pertenecen al contrato de la métrica.

• ¿Qué zona horaria define un día? Esta respuesta utiliza America/New_York. UTC movería eventos cercanos

a la medianoche local a una fecha de reporte diferente.

• ¿La ventana es de siete fechas de calendario o de 168 horas transcurridas? La consigna pide fechas de calendario local.

Una transición de horario de verano puede hacer que esas siete fechas contengan 167 o 169 horas transcurridas.

• ¿Ambos límites son inclusivos? El conjunto de usuarios cubre desde report_date - 6 hasta report_date.

Los filtros de marcas de tiempo de origen utilizan un rango semiabierto para evitar el doble conteo de la medianoche siguiente.

• ¿Deben aparecer las fechas faltantes? Sí. Genera las treinta fechas de reporte en lugar de derivar fechas únicamente

a partir de eventos existentes.

• ¿Qué tan completa está la tabla de eventos? Si los eventos pueden llegar con tres días de retraso, los resultados recientes son

provisionales o necesitan un watermark establecido. SQL por sí solo no puede hacer que una entrada incompleta sea definitiva.

• ¿Se requiere un distinct exacto? La consulta de la entrevista es exacta. A gran escala, una representación aproximada

de conjuntos combinables (mergeable-set) puede ser aceptable solo después de que se apruebe su contrato de margen de error.

• ¿Qué base de datos y escala aplican? La respuesta utiliza PostgreSQL. Un data warehouse puede utilizar otra función de date-spine

o primitivas de bitmap preservando la misma semántica de conjuntos.

Estructura de respuesta en 30 segundos

“Primero definiría los eventos activos, las exclusiones, America/New_York como límite del día y la granularidad de salida de una fila por fecha de reporte. Escanearía desde el 26 de mayo porque el 1 de junio necesita seis fechas anteriores, convertiría las marcas de tiempo a fechas locales y deduplicaría a una fila por usuario por fecha. Luego generaría del 1 de junio al 30 de junio con generate_series, haría un left join de cada fecha con la actividad desde date - 6 hasta esa fecha, y contaría usuarios distintos.

No sumaría los usuarios activos diarios porque un usuario activo en múltiples días se contaría repetidamente. Una ventana de seis filas también falla cuando faltan fechas y no crea una unión distinct. Probaría límites de medianoche, duplicados, fechas vacías y actividad de warm-up, y luego publicaría un watermark puntual o actualizaría las fechas recientes cuando lleguen eventos tardíos.”

Análisis detallado paso a paso

Paso 1: Fijar el contrato de la métrica y el rango de entrada

La salida solicitada comienza el 1 de junio, pero el escaneo del origen comienza el 26 de mayo. Leer únicamente filas de junio subestimaría las primeras seis fechas de reporte. El límite superior del origen es la medianoche local del 1 de julio; la actividad futura es irrelevante para una ventana previa que termina el 30 de junio.

Traduce esas medianoches locales a constantes timestamptz en el filtro. Esto mantiene el predicado sobre la columna indexada event_at. Convertir cada marca de tiempo de origen a una fecha dentro de la cláusula WHERE puede impedir que un índice de rango normal realice una poda útil.

Paso 2: Normalizar eventos a usuario-días distintos

Convierte cada instante elegible a su fecha de calendario de Nueva York solo después de aplicar el filtro acotado de marca de tiempo. Luego agrupa por user_id y fecha local. Un reintento de evento con un ID de fila diferente y veinte eventos de un usuario en un día se convierten todos en un único usuario-día.

Esta deduplicación no resuelve el problema final por sí sola. Si el mismo usuario está activo el 1 de junio y el 2 de junio, ambas filas de usuario-día deben permanecer disponibles para que la ventana previa de cualquiera de las dos fechas pueda incluir a ese usuario.

Paso 3: Generar un date spine completo

generate_series crea cada fecha de reporte independientemente de la presencia de eventos. Comenzar desde la tabla de eventos omitiría una fecha vacía, cambiaría el número de filas en un marco de ROWS y no dejaría ningún punto con valor cero en el panel. El spine es la granularidad de salida definitiva.

Paso 4: Contar la unión distinct para cada ventana

La consulta exacta directa es:

WITH params AS (
  SELECT
    DATE '2026-06-01' AS report_start,
    DATE '2026-06-30' AS report_end
),
activity_days AS (
  SELECT
    e.user_id,
    (e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
  FROM events AS e
  WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
    AND e.event_at <  TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
    AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
    AND e.is_internal_user = false
  GROUP BY
    e.user_id,
    (e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
  SELECT gs::date AS report_date
  FROM params AS p
  CROSS JOIN generate_series(
    p.report_start,
    p.report_end,
    INTERVAL '1 day'
  ) AS gs
)
SELECT
  d.report_date,
  COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
  ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;

El left join conserva las fechas de reporte vacías. COUNT(DISTINCT a.user_id) ignora el valor nulo producido por un left join sin coincidencias. El intervalo contiene exactamente siete valores de date: la fecha actual y seis predecesoras.

Paso 5: Demostrar por qué el atajo común de ventanas falla

Supongamos que el usuario A está activo el lunes y el martes, mientras que el usuario B está activo solo el martes. El DAU es 1 y 2, pero la unión distinct de dos días es 2, no 3. Una vez que los conteos diarios reemplazan la identidad del usuario, SQL no puede descubrir que A aparece en ambos días.

Un marco de filas introduce otro error. Si el miércoles no tiene eventos y está ausente de la entrada, “seis filas anteriores” puede alcanzar ocho o más días de calendario hacia atrás. Un date spine soluciona los huecos del calendario, pero una suma móvil sobre DAU todavía cuenta dos veces las identidades. La operación correcta es unión primero, cardinalidad después.

Paso 6: Escalar sin cambiar la semántica

Para rangos moderados, indexa los datos crudos por event_at y redúcelos a usuario-días antes del join de intervalo de 7x. Una tabla de actividad diaria materializada con clave en (activity_date, user_id) evita volver a escanear eventos crudos. La poda de particiones debe incluir las fechas de warm-up.

Para un rango de reporte más largo, expande cada usuario-día a un máximo de siete fechas de reporte elegibles, acota esas fechas al rango solicitado y luego agrupa los usuarios distintos. Esto cambia la forma del join pero no la expansión de 7x en el peor caso. Los motores con conjuntos de mapas de bits exactos pueden unir mapas de bits de usuarios diarios; los sketches aproximados deben admitir unión de conjuntos y deben exponer el error medido. Sumar estimaciones diarias de HyperLogLog no es válido porque las cardinalidades aproximadas no se pueden sumar para obtener una unión.

Paso 7: Definir datos tardíos y verificación

Publica un watermark as_of con el resultado. Si la canalización acepta eventos con hasta tres días de retraso, actualiza al menos cada fecha de reporte cuya ventana de entrada de siete días interseque con los datos mutables. Un ID de evento estable ayuda a la deduplicación en la ingesta, mientras que la agrupación de usuario-día protege esta métrica de múltiples eventos elegibles; ninguno de los dos reemplaza el monitoreo de completitud.

Utiliza un oráculo construido manualmente con eventos duplicados, el mismo usuario en varias fechas, un usuario interno, un evento no elegible, eventos de warm-up del 26 y 31 de mayo, una fecha vacía, instantes de medianoche local en ambos lados y un límite de horario de verano. Compara cada fecha de salida con una unión de conjuntos simple a nivel de aplicación. Ejecuta EXPLAIN (ANALYZE, BUFFERS) en un volumen similar al de producción y verifica la poda de origen, la cardinalidad de usuario-día, la expansión del join, el tiempo de ejecución y el comportamiento de desbordamiento (spill).

Respuesta de muestra sólida

“Definiría el conjunto antes de escribir SQL: un usuario elegible tiene al menos un evento de producto aprobado, se excluyen los usuarios internos y un día significa America/New_York. Para la fecha de reporte D, el conjunto es cada usuario elegible con una fecha de actividad local entre D menos seis y D, inclusive. La salida debe contener las treinta fechas.

Filtraría las marcas de tiempo crudas desde la medianoche local del 26 de mayo hasta la medianoche local del 1 de julio, manteniendo el límite superior exclusivo. Después de filtrar, convierto a fechas locales y agrupo por usuario y fecha. Un spine de generate_series suministra del 1 de junio al 30 de junio. Cada fecha del spine hace un left join con los usuario-días en su intervalo previo, y COUNT(DISTINCT user_id) devuelve la cardinalidad de la unión.

Rechazaría SUM(DAU) porque las identidades activas en varias fechas se repiten, y rechazaría ROWS 6 PRECEDING porque las filas no son fechas del calendario y la agregación diaria descartó la identidad. Para escalar, materializaría (activity_date, user_id), podaría el rango de warm-up y consideraría uniones exactas de mapas de bits o una unión aproximada de conjuntos medida únicamente cuando no se requiera una salida exacta. El resultado lleva un watermark as_of, y los datos tardíos activan un recálculo acotado.”

Errores comunes

• Sumar siete valores de DAU → los usuarios recurrentes se cuentan una vez por fecha activa → **une las identidades de usuario

a través de la ventana, luego cuenta.**

• Usar ROWS 6 PRECEDING en fechas dispersas → seis filas pueden abarcar más de seis fechas anteriores → **genera

un calendar spine completo y expresa límites de calendario.**

• Escanear solo junio → las ventanas de principios de junio pierden la actividad de mayo → incluye el warm-up de seis días.
• Convertir con cast event_at en el filtro de origen → un índice de marca de tiempo ordinario puede no podar eficientemente →

filtra con límites semiabiertos de timestamptz antes de derivar la fecha local.

• Contar eventos crudos → los reintentos y el uso repetido inflan los usuarios → **deduplica a usuario-día y aun así

cuenta distinct a través de la ventana final.**

• Derivar fechas de salida a partir de eventos → las fechas vacías desaparecen → haz que el date spine sea la granularidad de salida.
• Llamar definitiva a la salida reciente → los eventos tardíos pueden cambiar el conjunto → **publica un watermark y actualiza

las ventanas afectadas.**

• Sumar cardinalidades diarias aproximadas → la suma de cardinalidades no puede eliminar el solapamiento → **combina un

sketch o bitmap con capacidad de conjuntos antes de estimar la unión.**

Preguntas de seguimiento y respuestas

Pregunta de seguimiento 1: ¿Qué cambia si la métrica significa las 168 horas anteriores?

Compara instantes en lugar de valores locales de date. Para cada instante de reporte, usa una ventana de marca de tiempo semiabierta como (report_at - interval '168 hours', report_at], especificando el límite exacto del producto. Alrededor de las transiciones de horario de verano, esto difiere de siete fechas del calendario de Nueva York. No renombres una definición con el nombre de la otra.

Pregunta de seguimiento 2: ¿Puede una función de ventana resolver el distinct móvil exacto por sí sola?

Un agregado de ventana es útil cuando el agregado se compone a partir de valores de fila, como una suma móvil. En este caso, el estado requerido es un conjunto con eliminaciones cuando las fechas antiguas salen de la ventana. La respuesta directa en PostgreSQL conserva las identidades y las une a las fechas de reporte. Un motor especializado puede exponer funciones de ventana con unión exacta de mapas de bits, pero eso es una característica del motor, no una razón para sumar conteos diarios.

Pregunta de seguimiento 3: ¿Cómo se actualiza después de que un evento llega con tres días de retraso?

Encuentra la fecha de actividad local A del evento. Puede afectar las fechas de reporte A hasta A más seis, intersecadas con el rango publicado. Recalcula o reemplaza solo esas particiones, avanza el watermark después de la conciliación y mantén la operación idempotente. Actualizar solo la fecha A pasa por alto seis ventanas posteriores.

Pregunta de seguimiento 4: ¿Cómo segmentarías por país?

Primero define si el país pertenece al evento, al perfil actual del usuario o a un perfil lentamente cambiante al momento de la actividad. Esa elección cambia la verdad histórica. Agrega el país elegido a la granularidad de usuario-día, al date spine, a la agrupación y al oráculo de validación. Un join con el perfil actual puede reescribir el historial cuando un usuario se muda.

Pregunta de seguimiento 5: ¿Qué pasa si el distinct exacto es demasiado costoso?

Mide primero la consulta exacta. Si el presupuesto de error aprobado permite la aproximación, almacena un sketch de conjunto combinable por fecha de actividad y segmento, une siete sketches y realiza una única estimación. Valida el sesgo y el error relativo frente a conjuntos exactos para segmentos de baja, normal y alta cardinalidad. Mantén el procesamiento exacto para facturación, elegibilidad u otras decisiones que no toleren errores de estimación.