Sección 1. SQL

Ejercicio I

Bases

Cuentas_clientes: (id_cliente, cuenta_id, canal, fecha_alta, estatus_cuenta).
Transacciones: (transaccion_id, fecha, id_cliente, cuenta_id, canal, monto, estatus_tx).
Saldos: (fecha_corte, id_cliente, cuenta_id, saldo_final).
Originacion: (contrato_id, id_cliente, fecha_aprobacion, monto_aprobado, estatus_contrato).

Esquema de las bases de datos

Dadas las variables de cada base, realiza el código en SQL para contestar las siguientes preguntas:

Calcula por canal los clientes únicos y monto total de las transacciones APROBADAS en 2025. Ordena por monto desc.

# Clientes únicos y monto total de transacciones aprobadas por canal en 2025
SELECT 
    canal,
    COUNT(DISTINCT id_cliente) AS clientes_unicos,
    SUM(monto) AS monto_total
FROM Transacciones
WHERE estatus_tx = 'APROBADA'
  AND YEAR(fecha) = 2025
GROUP BY canal
ORDER BY monto_total DESC;

Muestra la lista de todas las cuentas activas (estatus_cuenta=1) y el monto total de sus transacciones de 2025 (solo APROBADAS). Si no hubo transacciones, mostrar 0.

# Lista de cuentas activas con monto total (o 0) de transacciones aprobadas 2025
SELECT 
    c.cuenta_id,
    c.id_cliente,
    COALESCE(SUM(t.monto), 0) AS monto_total
FROM Cuentas_clientes c
LEFT JOIN Transacciones t
       ON c.cuenta_id = t.cuenta_id
      AND c.id_cliente = t.id_cliente
      AND t.estatus_tx = 'APROBADA'
      AND YEAR(t.fecha) = 2025
WHERE c.estatus_cuenta = 1
GROUP BY c.cuenta_id, c.id_cliente;

Para cada cliente, identifica su primera transacción APROBADA de 2025. Devuelve id_cliente, transaccion_id, fecha, monto.

# Primera transacción aprobada de cada cliente en 2025
WITH primera_tx AS (
    SELECT 
        id_cliente,
        transaccion_id,
        fecha,
        monto,
        ROW_NUMBER() OVER (PARTITION BY id_cliente ORDER BY fecha ASC) AS rn
    FROM Transacciones
    WHERE estatus_tx = 'APROBADA'
      AND YEAR(fecha) = 2025
)
SELECT id_cliente, transaccion_id, fecha, monto
FROM primera_tx
WHERE rn = 1;

Top 3 clientes por canal según monto total de las transacciones APROBADAS en 2025.

# Top 3 clientes por canal (monto total de aprobadas en 2025)
WITH total_cliente AS (
    SELECT 
        canal,
        id_cliente,
        SUM(monto) AS monto_total
    FROM Transacciones
    WHERE estatus_tx = 'APROBADA'
      AND YEAR(fecha) = 2025
    GROUP BY canal, id_cliente
)
SELECT canal, id_cliente, monto_total
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto_total DESC) AS rn
    FROM total_cliente
) t
WHERE rn <= 3;

Para contratos APROBADOS en 2025, calcula el monto transaccionado en canal YASTAS dentro de los 30 días posteriores a la fecha_aprobacion del contrato. Devuelve contrato_id, id_cliente, monto_30d.

# Monto transaccionado en Yastás dentro de 30 días de contratos aprobados 2025
SELECT 
    o.contrato_id,
    o.id_cliente,
    COALESCE(SUM(t.monto), 0) AS monto_30d
FROM Originacion o
LEFT JOIN Transacciones t
       ON o.id_cliente = t.id_cliente
      AND t.estatus_tx = 'APROBADA'
      AND t.canal = 'YASTAS'
      AND t.fecha BETWEEN o.fecha_aprobacion AND DATEADD(DAY, 30, o.fecha_aprobacion)
WHERE o.estatus_contrato = 'APROBADO'
  AND YEAR(o.fecha_aprobacion) = 2025
GROUP BY o.contrato_id, o.id_cliente;

Clasifica a los clientes por monto total de sus transacciones de 2025 (APROBADAS): ALTO ≥ 1000 MEDIO 500–999.99 BAJO < 500 Reporta solo buckets con ≥ 5 clientes. Muestra bucket, clientes, monto_total.

# Clasificación de clientes por bucket de monto 2025
WITH total_cliente AS (
    SELECT 
        id_cliente,
        SUM(monto) AS monto_total
    FROM Transacciones
    WHERE estatus_tx = 'APROBADA'
      AND YEAR(fecha) = 2025
    GROUP BY id_cliente
),
clasificacion AS (
    SELECT 
        id_cliente,
        monto_total,
        CASE 
            WHEN monto_total >= 1000 THEN 'ALTO'
            WHEN monto_total BETWEEN 500 AND 999.99 THEN 'MEDIO'
            ELSE 'BAJO'
        END AS bucket
    FROM total_cliente
)
SELECT bucket, COUNT(*) AS clientes, SUM(monto_total) AS monto_total
FROM clasificacion
GROUP BY bucket
HAVING COUNT(*) >= 5;

Para cada mes de 2025 y canal, identifica el cliente con mayor monto de transacciones (APROBADAS). Reporta solo la fecha con el formato “yyyyMM” y canal con ≥ 5 clientes con monto > 0

# Cliente con mayor monto por mes y canal (mínimo 5 clientes > 0)
WITH resumen AS (
    SELECT 
        FORMAT(fecha, 'yyyyMM') AS mes,
        canal,
        id_cliente,
        SUM(monto) AS monto_total
    FROM Transacciones
    WHERE estatus_tx = 'APROBADA'
      AND YEAR(fecha) = 2025
    GROUP BY FORMAT(fecha, 'yyyyMM'), canal, id_cliente
),
ranking AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY mes, canal ORDER BY monto_total DESC) AS rn,
           COUNT(DISTINCT id_cliente) OVER (PARTITION BY mes, canal) AS clientes_validos
    FROM resumen
    WHERE monto_total > 0
)
SELECT mes, canal, id_cliente, monto_total
FROM ranking
WHERE rn = 1
  AND clientes_validos >= 5;

Clasifica cada contrato APROBADO 2025 como ‘ACTIVO_30D’ si el cliente tuvo ≥1 transacción APROBADA en YASTAS dentro de 30 días posteriores al inicio del contrato; si no, ‘INACTIVO_30D’. Devuelve contrato_id, id_cliente, etiqueta.

# Clasificación de contratos (ACTIVO_30D vs INACTIVO_30D)
SELECT 
    o.contrato_id,
    o.id_cliente,
    CASE 
        WHEN COUNT(t.transaccion_id) > 0 THEN 'ACTIVO_30D'
        ELSE 'INACTIVO_30D'
    END AS etiqueta
FROM Originacion o
LEFT JOIN Transacciones t
       ON o.id_cliente = t.id_cliente
      AND t.estatus_tx = 'APROBADA'
      AND t.canal = 'YASTAS'
      AND t.fecha BETWEEN o.fecha_aprobacion AND DATEADD(DAY, 30, o.fecha_aprobacion)
WHERE o.estatus_contrato = 'APROBADO'
  AND YEAR(o.fecha_aprobacion) = 2025
GROUP BY o.contrato_id, o.id_cliente;

Ejercicio II

De acuerdo con el siguiente Modelo de Datos contesta las preguntas:

Modelo de datos (Esquema de base de datos)

Obtener solo el universo de empleados coincidentes entre la tabla PARTY y la tabla EMPLOYEE_BONUS, agrupando por Bonus_Type_Desc y sumarizando Bonus_Amt.

# Universo de empleados entre PARTY y EMPLOYEE_BONUS
SELECT 
    bt.Bonus_Type_Desc,
    SUM(b.Bonus_Amt) AS total_bonus
FROM PARTY AS p
INNER JOIN EMPLOYEE_BONUS AS b
    ON p.Party_Id = b.Party_Id
INNER JOIN BONUS_TYPE AS bt
    ON b.Bonus_Type_Cd = bt.Bonus_Type_Cd
GROUP BY 
    bt.Bonus_Type_Desc;

Obtener de la tabla PARTY los Party_Id que no estén en la tabla EMPLOYEE_METRIC y genera una bandera que indique 1 si Metric_Score es mayor a 5 y 0 en otro caso.

# Party_Id no presentes en EMPLOYEE_METRIC + bandera
SELECT 
    p.Party_Id,
    CASE 
        WHEN m.Metric_Score > 5 THEN 1
        ELSE 0
    END AS bandera
FROM PARTY p
LEFT JOIN EMPLOYEE_METRIC m
       ON p.Party_Id = m.Party_Id
WHERE m.Party_Id IS NULL;

Sección 2. Caso de Negocio: Piloto de campaña en 8 sucursales

Contexto: Yastás lanzará una campaña de comunicación de 12 semanas para aumentar las transacciones en canal Yastás. Se activará en 8 de 40 sucursales (piloto). Se comunicará a los clientes de Compartamos Banco que pueden asistir a un punto Yastás para realizar sus pagos de crédito. El canal de comunicación es SMS.

Objetivo de negocio: Incrementar transacciones de pago de crédito en Yastás reduciendo las transacciones en las sucursales objetivo. Conocer la transaccionalidad del cliente al que se le comunico y la comparación con el grupo control.

Describe qué análisis harías para medir el uplift incremental del piloto, qué KPIs vigilaría, cómo diseñaría el análisis, qué datos requiere y criterios de decisión para seguir o parar con la campaña.

Diseño del experimento

Objetivo de negocio: Reducir los costos generados por realizar transacciones en sucursales de Compartamos Banco.

Tipo de experimento: A/B testing (campaña de comunicación).

Unidad de análisis: Clientes de Compartamos Banco.

Unidad de aleatorización: De acuerdo con la página web de Gentera, Compartamos Banco cuenta con 140 sucursales en México, de las cuales se seleccionaron 40 para realizar la prueba piloto de una campaña de comunicación, 8 de ellas recibirán el tratamiento y las 32 restantes no lo recibirán.

Número de grupos: 2 (un grupo de tratamiento y un grupo de control). Si hubiera otro medio de comunicación como WhatsApp o correo electrónico, podrían haber más grupos de tratamiento, lo cual serviría para comparar e identificar si algún medio de comunicación es más efectivo para el objetivo del experimento.

Tratamiento: Mensaje de texto a clientes de Compartamos Banco en donde se informa que pueden pagar sus créditos en algún punto Yastás. Se asume que el mensaje se envía una sola vez a los clientes del grupo de tratamiento y que estos lo reciben y lo entienden. En caso contrario, sería difícil determinar el tiempo que tomó a un cliente cambiar de sucursal bancaria a un punto Yastás.

Duración: 12 semanas. La duración de la campaña es lo suficientemente larga para identificar a personas que “migren” del lugar en el que realizan su pago. Posiblemente el cambio no sea inmediato y dependa de la fecha en la que se envíe el SMS y la fecha de pago del crédito.

Consideraciones éticas: consentimiento informado. Se infiere que al firmar el contrato del crédito se acuerda que se pueden usar los números telefónicos de los clientes para enviar información importante.

KPIs a vigilar

Transacciones en puntos Yastás (volumen y monto).
Transacciones en sucursales (volumen y monto).
Tasa de migración = Transacciones Yastás / (Transacciones Yastás + Sucursal).
Incremento neto (uplift): diferencia de diferencias en la tasa de migración.
Clientes activos que usaron Yastás tras el SMS.

Datos requeridos

Transacciones (sucursal vs Yastás) por cliente, fecha, monto.
Identificación de clientes tratados (con SMS) y no tratados (sin SMS).
Variables de control: sexo, edad, ingresos, escolaridad, antigüedad de cuenta, historial de uso del canal. Características geográficas y socioeconómicas de las sucursales y puntos Yastás.

Análisis

Para analizar los resultados de la campaña de comunicación se puede aplicar la metodología de Diferencias en Diferencias, a fin de controlar por factores fijos en el tiempo (efectos no observados constantes).

Por ejemplo, suponiendo que antes de la campaña los clientes pertenecientes al grupo de tratamiento realizaban 10 mil transacciones en sucursal y 2 mil en puntos Yastás, después de la campaña realizan 6 mil en sucursal y 6 mil en Yastás, esto se traduce en un aumento a 4 mil transacciones en Yastás.

Luego, suponiendo que antes de la campaña los clientes pertenecientes al grupo de control realizaban 10 mil transacciones en sucursal y 2 mil en puntos Yastás, después de la campaña (por tendencia natural) realizan 9 mil en sucursal y 3 mil en puntos Yastás, esto se traduce en un aumento de mil transacciones a Yastás.

Uplift incremental en Yastás:

\[ Uplift = ∆ (Yastás_{tratamiento}) - ∆ (Yastás_{control}) \]

\[ = (6-2) - (3-2) \]

\[ = (4) - (1) \] \[ Uplift = 3 \]

A partir de lo anterior y verificando las pruebas de significancia estadística, se podría concluir que el SMS generó un incremento neto de 3 mil transacciones en puntos Yaestás.

Criterios de decisión

Seguir con la campaña:

Uplift incremental positivo y significativo en transacciones Yastás (ej. +10% en uso de Yastás).
Reducción de carga en sucursales (≥ 5%).
ROI positivo el ahorro operativo (y posibles ingresos adicionales) supera lo invertido en SMS.

Detener campaña:

No hay diferencia estadística con el grupo control.
Clientes no adoptan Yastás o se reducen pagos totales.
Hay efectos adversos (quejas, caída en el total de pagos).

Amenazas

Efecto spillovers

No todos reciben, leen o entienden el SMS.

Asignación a nivel sucursal y resultados a nivel cliente

Referencias

Duflo, E., Glennerster, R., & Kremer, M. (2007). Using Randomization in Development Economics Research: A Toolkit. Handbook of Development Economics.
Gentera. (s.f.). Compartamos Banco. https://www.gentera.com.mx/gentera
Holywell, S. (s.f.) SQL Style Guide. https://www.sqlstyle.guide/
Karlan, D., & Valdivia, M. (2011). Teaching Entrepreneurship: Impact of Business Training on Microfinance Clients and Institutions. Review of Economics and Statistics, 93(2), 510–527.
List, J. A. (2011). Why Economists Should Conduct Field Experiments and 14 Tips for Pulling One Off. Journal of Economic Perspectives.
Lusardi, A., & Mitchell, O. S. (2014). The Economic Importance of Financial Literacy: Theory and Evidence. Journal of Economic Literature, 52(1), 5–44.
Palfrey, T. R., & Porter, D. (1991). Guidelines for submitting data from economic experiments. Experimental Economics Handbook.

# NOTA DE COMPATIBILIDAD:
# Las consultas fueron escritas en sintaxis SQL Server (T-SQL).
# En caso de usarse MySQL o PostgreSQL, basta con adaptar funciones de fechas:
#   DATEADD → INTERVAL
#   FORMAT → DATE_FORMAT o TO_CHAR

Prueba Técnica: Líder Analítica de Negocios YTS

Daniel Cadena

2025-09-28