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 ( 4 years ago )
---------------- 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)
)
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
Revise this Paste