Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as SQL by registered user vvillacorta ( 1 year ago )
-----------------------NOTEBOOK 1 - CREACIÓN DE TABLAS BASE--------------
-----T1) TABLA CLIENES CON PRODUCTOS ACTIVOS Y PASIVOS
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_OBM_SALDO_DIARIO_AWS
CREATE TABLE d_mdl_vpc_disc.HM_OBM_SALDO_DIARIO_AWS
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/Segmentacion_BPE/1_TABLAS_FINALES/8_AUTOMATIZACION/HM_OBM_SALDO_DIARIO_AWS/'
)
AS (
WITH SALDOS_DIAS AS (
select periodo_val, max(fecha_saldo_dt) fecha_saldo_dt
from e_perm_aws.t_agg_vpc_saldos_diarios
group by periodo_val
)
select aa.periodo_val Periodo,aa.codigo_unico_cliente_cd,
max(case when aa.tipo_prod_dsc='DEPÓSITOS' then 1 else 0 end) flg_pasivo,
max(case when aa.tipo_prod_dsc<>'DEPÓSITOS' then 1 else 0 end) flg_activo,
sum(case when aa.tipo_prod_dsc='DEPÓSITOS' then saldo_actual_sol_mto else 0 end) saldo_punta_pasivo,
sum(case when aa.tipo_prod_dsc='DEPÓSITOS' then saldo_promedio_sol_mto else 0 end) saldo_promedio_pasivo,
sum(case when aa.tipo_prod_dsc<>'DEPÓSITOS' then saldo_promedio_sol_mto else 0 end) saldo_promedio_activo,
sum(case when aa.tipo_prod_dsc<>'DEPÓSITOS' then saldo_actual_sol_mto else 0 end) saldo_punta_activo,
count(distinct tipo_prod_dsc) nro_tipo_producto,
aa.periodo_val
from e_perm_aws.t_agg_vpc_saldos_diarios aa
inner join SALDOS_DIAS bb on aa.periodo_val=bb.periodo_val
and aa.fecha_saldo_dt=bb.fecha_saldo_dt
where CAST(aa.periodo_val AS VARCHAR) >=substr(replace(substr(cast(date_add('month', -9, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
and CAST(aa.periodo_val AS VARCHAR) <= '{periodo_ejecucion}'
group by aa.periodo_val, aa.codigo_unico_cliente_cd
)
-----T2) TABLA TOTAL BPE
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_VMVP_VPC_CLIENTE_RUC_UNICO
CREATE TABLE d_mdl_vpc_disc.HM_VMVP_VPC_CLIENTE_RUC_UNICO
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/Segmentacion_BPE/1_TABLAS_FINALES/8_AUTOMATIZACION/HM_VMVP_VPC_CLIENTE_RUC_UNICO/'
)
AS (
SELECT cuc_num cdounico,
CAST(year(fecha_dt) * 100 + month(fecha_dt)AS VARCHAR) periodo
FROM e_perm_aws.T_VPC_CLIENTE_BANCA_FINAL_HST
WHERE banca_final_dsc = 'BPE'
AND CAST(year(fecha_dt) * 100 + month(fecha_dt)AS VARCHAR) <= '{periodo_ejecucion}'
AND CAST(year(fecha_dt) * 100 + month(fecha_dt)AS VARCHAR) >=substr(replace(substr(cast(date_add('month', -9, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
AND fecha_dt IN (
SELECT max(fecha_dt) fecha_dt
FROM e_perm_aws.T_VPC_CLIENTE_BANCA_FINAL_HST
GROUP BY CAST(year(fecha_dt) * 100 + month(fecha_dt)AS INTEGER)
)
GROUP BY CAST(year(fecha_dt) * 100 + month(fecha_dt)AS VARCHAR), cuc_num
)
-----T3) CARTERA ACTIVA
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_VMVP_CART_ACTIVA_VPC_RUC_UNICO
CREATE TABLE d_mdl_vpc_disc.HM_VMVP_CART_ACTIVA_VPC_RUC_UNICO
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/Segmentacion_BPE/1_TABLAS_FINALES/8_AUTOMATIZACION/HM_VMVP_CART_ACTIVA_VPC_RUC_UNICO/'
)
AS (
WITH CDOUNICO_PERIODO AS (
SELECT
CAST(year(fecha_dt) * 100 + month(fecha_dt) AS VARCHAR) AS periodo,
cuc_num
FROM e_perm_aws.T_VPC_CLIENTE_BANCA_FINAL_HST
WHERE fecha_dt IN (
SELECT max(fecha_dt) AS fecha_dt
FROM e_perm_aws.T_VPC_CLIENTE_BANCA_FINAL_HST
GROUP BY CAST(year(fecha_dt) * 100 + month(fecha_dt) AS INTEGER)
)
AND banca_final_dsc = 'BPE'
)
SELECT
A.codigo_unico_cliente_cd AS cdounico,
CAST(A.periodo_val AS VARCHAR) AS periodo_cartera,
CAST(
(YEAR(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt))) * 100) +
MONTH(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt)))
AS VARCHAR
) AS periodo
FROM e_perm_aws.t_mst_vpc_cart_activa_mes A
INNER JOIN CDOUNICO_PERIODO B
ON A.codigo_unico_cliente_cd = B.cuc_num
AND CAST(A.periodo_val AS VARCHAR) = B.periodo
AND CAST(
(YEAR(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt))) * 100) +
MONTH(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt)))
AS VARCHAR
) <= '{periodo_ejecucion}'
AND CAST(
(YEAR(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt))) * 100) +
MONTH(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt)))
AS VARCHAR
) >=substr(replace(substr(cast(date_add('month', -9, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
GROUP BY A.periodo_val, A.codigo_unico_cliente_cd,CAST(
(YEAR(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt))) * 100) +
MONTH(DATE_ADD('month', 1, DATE_TRUNC('month', fecha_dt)))
AS VARCHAR
)
)
-----T4) TABLA UNIFICADA
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_OBM_UNION_BPE_CLI
CREATE TABLE d_mdl_vpc_disc.HM_OBM_UNION_BPE_CLI
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/Segmentacion_BPE/1_TABLAS_FINALES/8_AUTOMATIZACION/HM_OBM_UNION_BPE_CLI/'
)
AS (
SELECT periodo, cdounico,
max(flg_activo) flg_activo,
max(flg_pasivo) flg_pasivo,
periodo p_periodo
FROM (
SELECT periodo, cdounico, 0 flg_activo, 0 flg_pasivo
FROM disc_comercial.HM_VMVP_VPC_CLIENTE_RUC_UNICO
WHERE CAST(periodo AS VARCHAR) >= substr(replace(substr(cast(date_add('month', -9, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
UNION
SELECT periodo, cdounico, 0 flg_activo, 0 flg_pasivo
FROM disc_comercial.HM_VMVP_CART_ACTIVA_VPC_RUC_UNICO
WHERE CAST(periodo AS VARCHAR) >= substr(replace(substr(cast(date_add('month', -9, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
UNION
SELECT CAST(periodo AS VARCHAR(6)) periodo, codigo_unico_cliente_cd cdounico, flg_activo, flg_pasivo
FROM disc_comercial.HM_OBM_SALDO_DIARIO_AWS
WHERE CAST(periodo AS VARCHAR) >=substr(replace(substr(cast(date_add('month', -9, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
)
GROUP BY periodo, cdounico
ORDER BY periodo DESC, cdounico
)
-----T5) TABLA BASE DE PO
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_OBM_PO_BASE_BPE_V1
CREATE TABLE d_mdl_vpc_disc.HM_OBM_PO_BASE_BPE_V1
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/Segmentacion_BPE/1_TABLAS_FINALES/8_AUTOMATIZACION/HM_OBM_PO_BASE_BPE_V1/'
)
AS (
SELECT periodo,
cdounico,
max(flg_activo) flg_activo,
max(flg_pasivo) flg_pasivo,
MAX(diagrama_venn) diagrama_venn,
MAX(saldo_punta_pasivo) saldo_punta_pasivo,
MAX(saldo_promedio_pasivo) saldo_promedio_pasivo,
MAX(saldo_punta_activo) saldo_punta_activo,
MAX(saldo_promedio_activo) saldo_promedio_activo,
MAX(flg_cartera_activa) flg_cartera_activa,
MAX(flg_VPC) flg_VPC,
MAX(nro_tipo_producto) nro_tipo_producto,
periodo p_periodo
FROM (
SELECT XX.periodo, XX.cdounico, XX.flg_activo, XX.flg_pasivo,
CASE WHEN VPC.cdounico IS NOT NULL AND CART.cdounico IS NOT NULL AND ACTPAS.codigo_unico_cliente_cd IS NOT NULL THEN 'INTERSECCION_3'
WHEN VPC.cdounico IS NOT NULL AND CART.cdounico IS NULL AND ACTPAS.codigo_unico_cliente_cd IS NULL THEN 'SOLO_VPC_CLIENTE'
WHEN VPC.cdounico IS NULL AND CART.cdounico IS NOT NULL AND ACTPAS.codigo_unico_cliente_cd IS NULL THEN 'SOLO_CARTERA_ACTIVA'
WHEN VPC.cdounico IS NULL AND CART.cdounico IS NULL AND ACTPAS.codigo_unico_cliente_cd IS NOT NULL THEN 'SOLO_ACTIVO_PASIVO'
WHEN VPC.cdounico IS NOT NULL AND CART.cdounico IS NOT NULL AND ACTPAS.codigo_unico_cliente_cd IS NULL THEN 'AMBOS_VPC_CLIENTE_CARTERA_ACTIVA'
WHEN VPC.cdounico IS NOT NULL AND CART.cdounico IS NULL AND ACTPAS.codigo_unico_cliente_cd IS NOT NULL THEN 'AMBOS_VPC_CLIENTE_ACTIVO_PASIVO'
WHEN VPC.cdounico IS NULL AND CART.cdounico IS NOT NULL AND ACTPAS.codigo_unico_cliente_cd IS NOT NULL THEN 'AMBOS_CARTERA_ACTIVA_ACTIVO_PASIVO'
ELSE 'X' END diagrama_venn,
ACTPAS.saldo_punta_pasivo,
ACTPAS.saldo_promedio_pasivo,
ACTPAS.saldo_punta_activo,
ACTPAS.saldo_promedio_activo,
CASE WHEN CART.cdounico IS NOT NULL THEN 1 ELSE 0 END flg_cartera_activa,
CASE WHEN VPC.cdounico IS NOT NULL THEN 1 ELSE 0 END flg_VPC,
ACTPAS.nro_tipo_producto nro_tipo_producto,
XX.periodo p_periodo
FROM disc_comercial.HM_OBM_UNION_BPE_CLI XX
LEFT JOIN disc_comercial.HM_VMVP_VPC_CLIENTE_RUC_UNICO VPC
ON XX.periodo = VPC.periodo
AND XX.cdounico = VPC.cdounico
LEFT JOIN disc_comercial.HM_VMVP_CART_ACTIVA_VPC_RUC_UNICO CART
ON XX.periodo = CART.periodo
AND XX.cdounico = CART.cdounico
LEFT JOIN disc_comercial.HM_OBM_SALDO_DIARIO_AWS ACTPAS
ON XX.periodo = CAST(ACTPAS.periodo AS VARCHAR(10))
AND XX.cdounico = CAST(ACTPAS.codigo_unico_cliente_cd AS VARCHAR(10))
)
GROUP BY periodo, cdounico
ORDER BY periodo DESC, cdounico
)
-----T6) TABLA PO
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_OBM_IND_BPE_V1
CREATE TABLE d_mdl_vpc_disc.HM_OBM_IND_BPE_V1
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/Segmentacion_BPE/1_TABLAS_FINALES/8_AUTOMATIZACION/HM_OBM_IND_BPE_V1/'
)
AS (
WITH AA AS (
select XX.periodo,
XX.cdounico,
CASE WHEN diagrama_venn IN ('INTERSECCION_3','AMBOS_VPC_CLIENTE_CARTERA_ACTIVA','AMBOS_VPC_CLIENTE_ACTIVO_PASIVO') THEN 1 ELSE 0 END FLG_PO,
CASE WHEN diagrama_venn IN ('INTERSECCION_3') THEN 1
WHEN diagrama_venn IN ('AMBOS_VPC_CLIENTE_ACTIVO_PASIVO') THEN 2
WHEN diagrama_venn IN ('AMBOS_VPC_CLIENTE_CARTERA_ACTIVA') THEN 3
ELSE 0 END TIPO_PO,
CASE WHEN LINEAS.CODUNICOCLI IS NOT NULL THEN 1 ELSE 0 END FLG_LINEA_ACTIVA,
CASE WHEN diagrama_venn IN ('AMBOS_VPC_CLIENTE_ACTIVO_PASIVO') THEN 1 ELSE 0 END FLG_CHURN_INACTIVIDAD,
CASE WHEN diagrama_venn IN ('AMBOS_VPC_CLIENTE_CARTERA_ACTIVA') THEN 1 ELSE 0 END FLG_CHURN_DESVINCULACION,
CASE WHEN SEGBPE1.periodo IS NOT NULL THEN SEGBPE1.segmento_final ELSE 'FUERA' END SEG_BPE_ANT
FROM disc_comercial.HM_OBM_PO_BASE_BPE_V1 XX
LEFT JOIN disc_comercial.HM_BPE_LINEAS_ACTIVAS LINEAS ON XX.periodo = LINEAS.periodo AND XX.cdounico = LINEAS.CODUNICOCLI
LEFT JOIN disc_comercial.HM_SEG_BPE_ACTUAL SEGBPE1 ON XX.periodo = SEGBPE1.periodo AND XX.cdounico = SEGBPE1.codunicocli
),
A AS (
SELECT periodo_val, cod_unico_cd cod_unico, count(distinct(tipo_campanha_dsc)) nro_tipo_campania,
MAX(CASE WHEN lower(tipo_campanha_dsc) like '%adq%' then 1 else 0 end) flg_adq,
MAX(CASE WHEN lower(tipo_campanha_dsc) like '%coloc%' then 1 else 0 end) flg_colocacion,
MAX(CASE WHEN lower(tipo_campanha_dsc) like '%impulso%' then 1 else 0 end) flg_impulso,
MAX(CASE WHEN lower(tipo_campanha_dsc) like '%cross%' then 1 else 0 end) flg_cross
FROM e_perm_aws.t_vpc_neg_empresas_vig
WHERE flg_activo = 1
AND cod_unico_cd is not null
AND cod_unico_cd not like ''
GROUP BY periodo_val, cod_unico_cd
)
SELECT XX.periodo,
XX.cdounico,
max(XX.flg_activo) flg_activo,
max(XX.flg_pasivo) flg_pasivo,
MAX(XX.diagrama_venn) diagrama_venn,
MAX(XX.saldo_punta_pasivo) saldo_punta_pasivo,
MAX(XX.saldo_promedio_pasivo) saldo_promedio_pasivo,
MAX(XX.saldo_punta_activo) saldo_punta_activo,
MAX(XX.saldo_promedio_activo) saldo_promedio_activo,
MAX(XX.flg_cartera_activa) flg_cartera_activa,
MAX(XX.flg_VPC) flg_VPC,
MAX(COALESCE(AA.FLG_PO,0)) FLG_PO,
MAX(COALESCE(AA.TIPO_PO,0)) TIPO_PO,
MAX(COALESCE(AA.FLG_LINEA_ACTIVA,0)) FLG_LINEA_ACTIVA,
MAX(COALESCE(AA.SEG_BPE_ANT,'FUERA')) SEG_BPE_ANT,
MAX(COALESCE(AA.FLG_CHURN_INACTIVIDAD,0)) FLG_CHURN_INACTIVIDAD,
MAX(COALESCE(AA.FLG_CHURN_DESVINCULACION,0)) FLG_CHURN_DESVINCULACION,
MAX(COALESCE(A.nro_tipo_campania,0)) NRO_TIPO_CAMPANIA,
MAX(COALESCE(A.flg_adq,0)) FLG_ADQ,
MAX(COALESCE(A.flg_colocacion,0)) FLG_COLOCACION,
MAX(COALESCE(A.flg_impulso,0)) FLG_IMPULSO,
MAX(COALESCE(A.flg_cross,0)) FLG_CROSS,
MAX(COALESCE(nro_tipo_producto,0)) nro_tipo_producto,
XX.p_periodo
FROM disc_comercial.HM_OBM_PO_BASE_BPE_V1 XX
LEFT JOIN AA ON AA.periodo= XX.periodo AND AA.cdounico= XX.cdounico
LEFT JOIN A ON A.periodo_val= XX.periodo
AND A.cod_unico= XX.cdounico
GROUP BY XX.periodo, XX.cdounico,XX.p_periodo
ORDER BY XX.periodo DESC, XX.cdounico
)
Revise this Paste