Psst.. new poll here.
[email protected] web/email now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted as SQL by registered user vvillacorta ( 3 weeks 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