---------------- DEPENDENCIAS -----------------------------------------------------------------------
SELECT max(periodo_val) from e_perm_aws.T_FACT_VPC_DETALLE_CLIENTE_RCC
SELECT max(periodo_val) from e_perm_aws.T_FACT_VPC_AGG_CLI_DESEMBOLSO_HST
SELECT max(periodo_val) from e_perm_aws.T_FACT_VPC_AGG_SOW_BPE
SELECT max(periodo) from e_perm_aws.T_FACT_VPC_DESEMBOLSO_MERCADO
select max(periodo_val) from e_perm_aws.t_fact_vpc_agg_sunat_reniec
--------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------- UNIVERSO A SCOREAR
DROP TABLE IF EXISTS d_mdl_vpc_disc.mm_mercado
-- No correr si ya se lee bien la data . si el select de la linea 32
CREATE EXTERNAL TABLE d_mdl_vpc_disc.mm_mercado
(
`periodo` string,
`cod_sbs` string,
`num_doc` string,
`persona_natural` int,
`persona_juridica` int,
`key_value` string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://sagemaker-us-east-1-058528764918/vpc/propension/market/MM_MERCADO/'
TBLPROPERTIES (
'skip.header.line.count'='1',
'has_encrypted_data'='false'
);
select count(1) from d_mdl_vpc_disc.mm_mercado
WHERE key_value not like '' and key_value is not null
SELECT * from d_mdl_vpc_disc.mercado_202111
where 1 = 1
and (cod_sbs like '' or cod_sbs = '.' or cod_sbs = 'SV')
limit 100
-------------------------------------------------------------------------------------------------------- SUNAT
--WHERE COD_SBS_RRLL_1 = '0185292029'
SELECT * FROM e_perm_aws.t_fact_vpc_agg_sunat_reniec LIMIT 5
SELECT * FROM d_mdl_vpc_disc.MM_SUNAT_PRICING LIMIT 5
DROP TABLE IF EXISTS d_mdl_vpc_disc.MM_SUNAT
CREATE TABLE d_mdl_vpc_disc.MM_SUNAT
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo_informacion'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/MM_SUNAT/'
)
AS (
SELECT
C.periodo periodo_val,
C.key_value numruc_val,
C.cod_unico_val,
C.tip_contribuyente_val,
--C.tip_persona_val,
C.condicion_domicilio,
C.estado_contribuyente,
d.contabilidad,
d.facturacion,
d.comercio_exterior,
C.estado,
C.ciiu_val,
C.ubigeo_val,
C.num_trabajadores,
C.monto_deuda_tributo_amt,
C.cnt_deudas_tributarias,
C.cnt_rrll,
C.tiempo_baja,
C.tiempo_alta,
C.promedio_edad_rrll,
C.tenencia_maxima_rrll,
C.rango_ventas,
C.cod_sbs_empresa_1,
C.cod_sbs_empresa_2,
C.cod_sbs_empresa_3,
C.cod_sbs_empresa_4,
C.cod_sbs_empresa_5,
C.cod_sbs_empresa_6,
C.cod_sbs_rrll_1,
C.cod_sbs_rrll_2,
C.cod_sbs_rrll_3,
C.cod_sbs_rrll_4,
C.cod_sbs_rrll_5,
C.cod_sbs_rrll_6,
C.periodo p_periodo_informacion
FROM d_mdl_vpc_disc.MM_SUNAT_PRICING C--e_perm_aws.t_fact_vpc_agg_sunat_reniec
left join d_mdl_vpc_disc.MM_SUNAT_LAST_FOTO_VERSION_ANTERIOR d
on c.key_value = d.numruc_val
WHERE periodo = (select max(periodo) from d_mdl_vpc_disc.MM_SUNAT_PRICING)
)
select count(1) from d_mdl_vpc_disc.MM_SUNAT
SELECT distinct(periodo_val) from d_mdl_vpc_disc.MM_SUNAT
-------------------------------------------------------------------------------------------------------- NUEVO MES
SELECT * FROM d_mdl_vpc_disc.mm_mercado LIMIT 5
SELECT * FROM d_mdl_vpc_disc.MM_SUNAT LIMIT 5
DROP TABLE IF EXISTS d_mdl_vpc_disc.MM_NUEVO_MES
CREATE TABLE d_mdl_vpc_disc.MM_NUEVO_MES
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo_informacion'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/MM_NUEVO_MES/'
)
AS (
SELECT
M.num_doc,
M.cod_sbs,
M.persona_natural,
M.persona_juridica,
S.*
FROM d_mdl_vpc_disc.mm_mercado M
LEFT JOIN d_mdl_vpc_disc.MM_SUNAT S
ON M.key_value = S.numruc_val
WHERE 1 = 1
and M.key_value is not null
and M.key_value not like ''
and M.key_value != '.'
and S.numruc_val is not null
)
select count(1) from d_mdl_vpc_disc.MM_NUEVO_MES
WHERE numruc_val is not null
select distinct(periodo_val) from d_mdl_vpc_disc.MM_NUEVO_MES
SELECT * FROM d_mdl_vpc_disc.MM_NUEVO_MES LIMIT 100
-------------------------------------------------------------------------------------------------------- DETALLE
DROP TABLE IF EXISTS d_mdl_vpc_disc.MM_DETALLE_RCC
CREATE TABLE d_mdl_vpc_disc.MM_DETALLE_RCC
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_fecha_sbs_dt'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/MM_DETALLE_RCC/'
)
AS (
SELECT * FROM e_perm_aws.T_FACT_VPC_DETALLE_CLIENTE_RCC
WHERE 1 = 1
and periodo_val = (select max(periodo_val) from e_perm_aws.T_FACT_VPC_DETALLE_CLIENTE_RCC)
)
select count(1) from d_mdl_vpc_disc.MM_DETALLE_RCC
select distinct(periodo_val) from d_mdl_vpc_disc.MM_DETALLE_RCC
------------------------------------------------------------------------------------------- UNION RCC
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_UNION_RCC
CREATE TABLE d_mdl_vpc_disc.HM_UNION_RCC
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/HM_UNION_RCC/'
)
AS (
select * FROM
(
SELECT B.*,
R.cod_sbs_val,
coalesce(R.nro_tipo_entidad, 0) nro_tipo_entidad,
coalesce(R.nro_entidades, 0) nro_entidades, ------------------------este
coalesce(R.nroregs_coloc_directas_bcos, 0) nroregs_coloc_directas_bcos, -------------------este
coalesce(R.porc_coloc_direct_vig_cmpt, 0) porc_coloc_direct_vig_cmpt, --------------este
coalesce(R.saldo_coloc_direct_tc, 0) saldo_coloc_direct_tc, -------------------------este
B.periodo_val p_periodo
FROM d_mdl_vpc_disc.MM_NUEVO_MES B
LEFT JOIN d_mdl_vpc_disc.MM_DETALLE_RCC R
ON R.cod_sbs_val = B.cod_sbs_empresa_1
WHERE 1 = 1
and B.numruc_val is not null
)
UNION
(
SELECT B.*,
R.cod_sbs_val,
coalesce(R.nro_tipo_entidad, 0) nro_tipo_entidad,
coalesce(R.nro_entidades, 0) nro_entidades, ------------------------este
coalesce(R.nroregs_coloc_directas_bcos, 0) nroregs_coloc_directas_bcos, -------------------este
coalesce(R.porc_coloc_direct_vig_cmpt, 0) porc_coloc_direct_vig_cmpt, --------------este
coalesce(R.saldo_coloc_direct_tc, 0) saldo_coloc_direct_tc, -------------------------este
B.periodo_val p_periodo
FROM d_mdl_vpc_disc.MM_NUEVO_MES B
INNER JOIN d_mdl_vpc_disc.MM_DETALLE_RCC R
ON R.cod_sbs_val = B.cod_sbs_empresa_2
WHERE 1 = 1
and B.numruc_val is not null
)
UNION
(
SELECT B.*,
R.cod_sbs_val,
coalesce(R.nro_tipo_entidad, 0) nro_tipo_entidad,
coalesce(R.nro_entidades, 0) nro_entidades, ------------------------este
coalesce(R.nroregs_coloc_directas_bcos, 0) nroregs_coloc_directas_bcos, -------------------este
coalesce(R.porc_coloc_direct_vig_cmpt, 0) porc_coloc_direct_vig_cmpt, --------------este
coalesce(R.saldo_coloc_direct_tc, 0) saldo_coloc_direct_tc, -------------------------este
B.periodo_val p_periodo
FROM d_mdl_vpc_disc.MM_NUEVO_MES B
INNER JOIN d_mdl_vpc_disc.MM_DETALLE_RCC R
ON R.cod_sbs_val = B.cod_sbs_empresa_3
WHERE 1 = 1
and B.numruc_val is not null
)
UNION
(
SELECT B.*,
R.cod_sbs_val,
coalesce(R.nro_tipo_entidad, 0) nro_tipo_entidad,
coalesce(R.nro_entidades, 0) nro_entidades, ------------------------este
coalesce(R.nroregs_coloc_directas_bcos, 0) nroregs_coloc_directas_bcos, -------------------este
coalesce(R.porc_coloc_direct_vig_cmpt, 0) porc_coloc_direct_vig_cmpt, --------------este
coalesce(R.saldo_coloc_direct_tc, 0) saldo_coloc_direct_tc, -------------------------este
B.periodo_val p_periodo
FROM d_mdl_vpc_disc.MM_NUEVO_MES B
INNER JOIN d_mdl_vpc_disc.MM_DETALLE_RCC R
ON R.cod_sbs_val = B.cod_sbs_rrll_1
WHERE 1 = 1
and B.numruc_val is not null
and B.cod_sbs_empresa_1 like ''
and B.persona_natural = 1
)
UNION
(
SELECT B.*,
R.cod_sbs_val,
coalesce(R.nro_tipo_entidad, 0) nro_tipo_entidad,
coalesce(R.nro_entidades, 0) nro_entidades, ------------------------este
coalesce(R.nroregs_coloc_directas_bcos, 0) nroregs_coloc_directas_bcos, -------------------este
coalesce(R.porc_coloc_direct_vig_cmpt, 0) porc_coloc_direct_vig_cmpt, --------------este
coalesce(R.saldo_coloc_direct_tc, 0) saldo_coloc_direct_tc, -------------------------este
B.periodo_val p_periodo
FROM d_mdl_vpc_disc.MM_NUEVO_MES B
INNER JOIN d_mdl_vpc_disc.MM_DETALLE_RCC R
ON R.cod_sbs_val = B.cod_sbs_rrll_2
WHERE 1 = 1
and B.numruc_val is not null
and B.cod_sbs_empresa_1 like ''
and B.persona_natural = 1
)
UNION
(
SELECT B.*,
R.cod_sbs_val,
coalesce(R.nro_tipo_entidad, 0) nro_tipo_entidad,
coalesce(R.nro_entidades, 0) nro_entidades, ------------------------este
coalesce(R.nroregs_coloc_directas_bcos, 0) nroregs_coloc_directas_bcos, -------------------este
coalesce(R.porc_coloc_direct_vig_cmpt, 0) porc_coloc_direct_vig_cmpt, --------------este
coalesce(R.saldo_coloc_direct_tc, 0) saldo_coloc_direct_tc, -------------------------este
B.periodo_val p_periodo
FROM d_mdl_vpc_disc.MM_NUEVO_MES B
INNER JOIN d_mdl_vpc_disc.MM_DETALLE_RCC R
ON R.cod_sbs_val = B.cod_sbs
WHERE 1 = 1
and B.numruc_val is not null
and B.cod_sbs_empresa_1 like ''
and B.cod_sbs_rrll_1 != B.cod_sbs
and B.cod_sbs_rrll_2 != B.cod_sbs
)
)
SELECT count(1) FROM d_mdl_vpc_disc.HM_UNION_RCC
LIMIT 50
SELECT * FROM d_mdl_vpc_disc.HM_UNION_RCC LIMIT 100
-------------------------------------------------------------------------------------------------------- CONSOLIDADO
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_NUEVO_MES_AGENDA_CREDITOS_CAMPANIAS
CREATE TABLE d_mdl_vpc_disc.HM_NUEVO_MES_AGENDA_CREDITOS_CAMPANIAS
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo_v'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/HM_NUEVO_MES_AGENDA_CREDITOS_CAMPANIAS/'
)
AS (
SELECT U.num_doc,
U.cod_sbs_val,
U.cod_sbs,
U.periodo_val,
U.numruc_val,
U.cod_unico_val,
U.tip_contribuyente_val,
--U.tip_persona_val,
U.condicion_domicilio,
U.estado_contribuyente,
U.contabilidad,
U.facturacion,
U.comercio_exterior,
U.estado,
U.ciiu_val,
U.ubigeo_val,
U.num_trabajadores,
U.monto_deuda_tributo_amt,
U.cnt_deudas_tributarias,
U.cnt_rrll,
U.tiempo_baja,
U.tiempo_alta,
U.promedio_edad_rrll,
U.tenencia_maxima_rrll,
M.BANCA_FIN_VAL,
U.nro_entidades,
U.rango_ventas,
U.saldo_coloc_direct_tc,
U.nroregs_coloc_directas_bcos,
U.porc_coloc_direct_vig_cmpt,
coalesce(M.facturacion_prom_ult9m_mnt, 0.0) facturacion_prom_ult9m_mnt, --------------------este
coalesce(M.deuda_sf_prom_ult9m, 0.0) deuda_sf_prom_ult9m, -------------------este
coalesce(M.tend_facturacion_ult_trim_mnt, 0.0) tend_facturacion_ult_trim_mnt, ------------- este
coalesce(M.tend_facturacion_ult_mes_mnt, 0.0) tend_facturacion_ult_mes_mnt,
coalesce(M.tend_deuda_agrar_ult_trim_mnt, 0.0) tend_deuda_agrar_ult_trim_mnt,
coalesce(M.tend_deuda_agrar_ult_mes_mnt, 0.0) tend_deuda_agrar_ult_mes_mnt,
coalesce(M.tend_deuda_sf_ult_trim_mnt, 0.0) tend_deuda_sf_ult_trim_mnt, ------------------ este
coalesce(D.MONTO_ADQUIRIDO_U6_AMT, 0.0) MONTO_ADQUIRIDO_U6_AMT, ------------- este
coalesce(D.MONTO_PAGADO_ULT_RCC_AMT, 0.0) MONTO_PAGADO_ULT_RCC_AMT,
coalesce(D.MONTO_PAGADO_U3_AMT, 0.0) MONTO_PAGADO_U3_AMT,
coalesce(D.MONTO_PAGADO_U6_AMT, 0.0) MONTO_PAGADO_U6_AMT, -----------------------este
coalesce(S.SALDO_COLOC_VIG_OTROS_BANCOS_AMT, 0.0) SALDO_COLOC_VIG_OTROS_BANCOS_AMT, ---------------este
coalesce(S.SOW_IBK, 0.0) SOW_IBK,
coalesce(S.SOW_OTROS_BANCOS, 0.0) SOW_OTROS_BANCOS,
coalesce(S.SOW_CAJAS, 0.0) SOW_CAJAS,
coalesce(T.tendencia_nro_entidades, 0) TENDENCIA_NRO_ENTIDADES_V2,
coalesce(T.tendencia_nro_coloc_direct_bancos, 0) TENDENCIA_NRO_COLOC_DIRECT_BANCOS_V2, ---------------este
coalesce(H.avg_saldo_reactiva_u6m, 0.0) PROM_REACTIVA_U6M, ----------------------------este
coalesce(H.avg_saldo_garantias_u12m, 0.0) PROM_GAR_u12m, ------------------------------------------------este
U.p_periodo p_periodo_v
FROM d_mdl_vpc_disc.HM_UNION_RCC U
LEFT JOIN e_perm_aws.T_FACT_VPC_DESEMBOLSO_MERCADO M
ON U.cod_sbs_val = M.cod_sbs_val and M.periodo = (select max(periodo) from e_perm_aws.T_FACT_VPC_DESEMBOLSO_MERCADO)
LEFT JOIN e_perm_aws.T_FACT_VPC_AGG_CLI_DESEMBOLSO_HST D
ON U.cod_sbs_val = D.cod_sbs_val and D.periodo_val = (select max(periodo_val) from e_perm_aws.T_FACT_VPC_AGG_CLI_DESEMBOLSO_HST)
LEFT JOIN e_perm_aws.T_FACT_VPC_AGG_SOW_BPE S
ON U.cod_sbs_val = S.cod_sbs_val and S.periodo_val = (select max(periodo_val) from e_perm_aws.T_FACT_VPC_AGG_SOW_BPE)
LEFT JOIN e_perm_aws.t_fact_vpc_historico_rcc H
ON U.cod_sbs_val = H.cod_sbs_val and H.fecha_sbs_dt = (select max(fecha_sbs_dt) from e_perm_aws.t_fact_vpc_historico_rcc)
LEFT JOIN e_perm_aws.t_fact_vpc_tendencia_rcc T
ON U.cod_sbs_val = T.cod_sbs_val
and cast(T.periodo_val as varchar) = (select max(cast(periodo_val as varchar)) from e_perm_aws.t_fact_vpc_tendencia_rcc)
)
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------Add a code snippet to your website: www.paste.org