Notes

PASO 1 :
CREATE OR REPLACE TABLE catalog_lhcl_prod_bcp.bcp_edv_expclie_001.tmp_interacciones_relevantes AS
SELECT DISTINCT
hi.codclavepartycli,
hi.fecinteraccion
FROM catalog_lhcl_prod_bcp.bcp_udv_int_vu.h_interaccionagente hi
JOIN (
SELECT DISTINCT codclavepartycli, fecha_encuesta
FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_v2
) base
ON hi.codclavepartycli = base.codclavepartycli
AND hi.fecinteraccion BETWEEN DATE_SUB(base.fecha_encuesta, 180) AND DATE_ADD(base.fecha_encuesta, 90);
--------------------------------------------------------------------------------------------------------------
PASO 2:
CREATE OR REPLACE TABLE catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_var_comportamiento AS
WITH base AS (
SELECT
ie.CIC,
ie.codclavepartycli,
ie.fecha_encuesta
FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_v2 ie
),
interacciones AS (
SELECT
codclavepartycli,
fecinteraccion
FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.tmp_interacciones_relevantes
),
agregados AS (
SELECT
b.CIC,
b.codclavepartycli,
b.fecha_encuesta,

-- Frecuencia últimos 3 meses
COUNT(CASE
WHEN i.fecinteraccion BETWEEN DATE_SUB(b.fecha_encuesta, 90) AND DATE_SUB(b.fecha_encuesta, 1)
THEN 1 END) AS frecuencia_agente_ult_3m,

-- Frecuencia entre 90 y 180 días antes
COUNT(CASE
WHEN i.fecinteraccion BETWEEN DATE_SUB(b.fecha_encuesta, 180) AND DATE_SUB(b.fecha_encuesta, 91)
THEN 1 END) AS frecuencia_prom_anterior,

-- Fecha de última visita previa
MAX(CASE
WHEN i.fecinteraccion < b.fecha_encuesta
THEN i.fecinteraccion END) AS ultima_visita,

-- Interacciones posteriores a encuesta (para definir fuga)
COUNT(CASE
WHEN i.fecinteraccion > b.fecha_encuesta AND i.fecinteraccion <= DATE_ADD(b.fecha_encuesta, 90)
THEN 1 END) AS interacciones_post_encuesta

FROM base b
LEFT JOIN interacciones i
ON b.codclavepartycli = i.codclavepartycli
GROUP BY b.CIC, b.codclavepartycli, b.fecha_encuesta
)

SELECT
*,

-- Variación porcentual entre periodos
CASE
WHEN frecuencia_prom_anterior = 0 THEN 0
ELSE (frecuencia_agente_ult_3m - frecuencia_prom_anterior) * 100.0 / frecuencia_prom_anterior
END AS variacion_frecuencia,

-- Días desde última visita
DATEDIFF(fecha_encuesta, ultima_visita) AS dias_desde_ultima_visita,

-- Flag si el uso bajó más de 30%
CASE
WHEN frecuencia_prom_anterior = 0 THEN 0
WHEN frecuencia_agente_ult_3m < 0.7 * frecuencia_prom_anterior THEN 1
ELSE 0
END AS flag_disminucion_uso,

-- Fuga si no regresó en 90 días tras encuesta
CASE
WHEN interacciones_post_encuesta = 0 THEN 1
ELSE 0
END AS fuga_agentes

FROM agregados;

SELECT * FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_var_comportamiento
-------------------------------------------------------------------------------------
PASO 3 (nro_reclamos_ult_3_meses) :

CREATE OR REPLACE TABLE catalog_lhcl_prod_bcp.bcp_edv_expclie_001.tmp_reclamos_ult_3m AS
SELECT
ie.codclavepartycli,
ie.fecha_encuesta,
COUNT(r.codreclamo) AS nro_reclamos_ult_3_meses
FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_var_comportamiento ie
LEFT JOIN catalog_lhcl_prod_bcp.bcp_udv_int_vu.h_reclamo r
ON r.codclavepartycli = ie.codclavepartycli
AND r.fecreclamo BETWEEN DATE_SUB(ie.fecha_encuesta, 90) AND DATE_SUB(ie.fecha_encuesta, 1)
GROUP BY ie.codclavepartycli, ie.fecha_encuesta;

CREATE OR REPLACE TABLE catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_var_comportamiento_reclamos AS
SELECT
base.*, -- Todo lo que ya habías generado antes
COALESCE(r.nro_reclamos_ult_3_meses, 0) AS nro_reclamos_ult_3_meses -- Reclamos (0 si no hay)
FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_var_comportamiento base
LEFT JOIN catalog_lhcl_prod_bcp.bcp_edv_expclie_001.tmp_reclamos_ult_3m r
ON base.codclavepartycli = r.codclavepartycli
AND base.fecha_encuesta = r.fecha_encuesta;

SELECT * FROM catalog_lhcl_prod_bcp.bcp_edv_expclie_001.Interaccion_encuestas_var_comportamiento_reclamos