----------------  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