DROP TABLE IF EXISTS d_mdl_vpc_disc.UNIVERSO_PREVIO_3

CREATE TABLE d_mdl_vpc_disc.UNIVERSO_PREVIO_3
    WITH ( format = 'Parquet', 
             parquet_compression = 'SNAPPY', 
             partitioned_by = ARRAY['p_periodo'],
             external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/UNIVERSO_PREVIO_3/'
           )
    AS (

SELECT AA.periodo_campania
       ,AA.periodo_ejecucion
       ,AA.num_documento
       ,SUM(AA.nro_tlv_ibk_p1_p2_p3) nro_tlv_ibk_p1_p2_p3
       ,SUM(AA.nro_tlv_bpe_p1_p2_p3) nro_tlv_bpe_p1_p2_p3
       ,SUM(AA.nro_lo_pensara_ibk_p1_p2_p3) nro_lo_pensara_ibk_p1_p2_p3 
       ,MAX(SS.cant_clientes_principalidad_retail) cant_clientes_principalidad_retail
       ,SUM(AA.nro_acepta_campana_ibk_p1) nro_acepta_campana_ibk_p1
       ,SUM(AA.nro_no_acepta_campana_bpe_p1_p2_p3) nro_no_acepta_campana_bpe_p1_p2_p3
       ,MAX(SS.ciiu_val) ciiu_val
       ,MAX(SS.promedio_edad_rrll) promedio_edad_rrll
       ,MAX(SS.saldo_prom_tot_activo_rrll) saldo_prom_tot_activo_rrll
       ,MAX(SS.producto_maximo_rrll) producto_maximo_rrll
       ,SUM(AA.nro_acepta_campana_ibk_p1_p2_p3) nro_acepta_campana_ibk_p1_p2_p3 
       ,MAX(SS.tiempo_alta) tiempo_alta
       ,CASE WHEN MAX(SS.numruc_val) IS NOT NULL THEN 1 ELSE 0 END flg_tiene_info_sunat
       ,MAX(SS.flg_pj) flg_pj
       ,MAX(SS.flg_pn) flg_pn
        ,MAX(SS.cod_sbs_empresa_1) cod_sbs_empresa_1
        ,MAX(SS.cod_sbs_empresa_2) cod_sbs_empresa_2
        ,MAX(SS.cod_sbs_empresa_3) cod_sbs_empresa_3
        ,MAX(SS.cod_sbs_empresa_4) cod_sbs_empresa_4
        ,MAX(SS.cod_sbs_empresa_5) cod_sbs_empresa_5
        ,MAX(SS.cod_sbs_empresa_6) cod_sbs_empresa_6
        ,MAX(SS.cod_sbs_rrll_1) cod_sbs_rrll_1
        ,MAX(SS.cod_sbs_rrll_2) cod_sbs_rrll_2
        ,MAX(SS.cod_sbs_rrll_3) cod_sbs_rrll_3
        ,MAX(SS.cod_sbs_rrll_4) cod_sbs_rrll_4
        ,MAX(SS.cod_sbs_rrll_5) cod_sbs_rrll_5
        ,MAX(SS.cod_sbs_rrll_6) cod_sbs_rrll_6
        ,MAX(SS.cod_unico_val) cod_unico_val
        ,MAX(SS.tip_contribuyente_val) tip_contribuyente_val
       ,AA.periodo_ejecucion p_periodo
FROM(

    SELECT PRESENTE.periodo_val periodo_val
           ,PRESENTE.periodo_campania periodo_campania
           ,PRESENTE.periodo_ejecucion periodo_ejecucion
           ,PRESENTE.num_documento
           ,coalesce(P1.flg_tlv_ibk, 0) + coalesce(P2.flg_tlv_ibk, 0) +  coalesce(P3.flg_tlv_ibk, 0) nro_tlv_ibk_p1_p2_p3
           ,coalesce(P1.flg_tlv_bpe, 0) + coalesce(P2.flg_tlv_bpe, 0) +  coalesce(P3.flg_tlv_bpe, 0) nro_tlv_bpe_p1_p2_p3
           ,coalesce(P1.flg_lo_pensara_ibk, 0) + coalesce(P2.flg_lo_pensara_ibk, 0) +  coalesce(P3.flg_lo_pensara_ibk, 0) nro_lo_pensara_ibk_p1_p2_p3
           ,coalesce(P1.flg_acepta_campana_ibk, 0) nro_acepta_campana_ibk_p1
           ,coalesce(P1.flg_acepta_campana_ibk, 0) + coalesce(P2.flg_acepta_campana_ibk, 0) +  coalesce(P3.flg_acepta_campana_ibk, 0) nro_acepta_campana_ibk_p1_p2_p3
           ,coalesce(P1.flg_no_acepta_campana_bpe, 0) + coalesce(P2.flg_no_acepta_campana_bpe, 0) +  coalesce(P3.flg_no_acepta_campana_bpe, 0) nro_no_acepta_campana_bpe_p1_p2_p3
          ,PRESENTE.periodo_val p_periodo
              
    FROM (
          SELECT 
          substr(replace(substr(cast(date_add('month', +1, date_parse(periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) periodo_val,
          substr(replace(substr(cast(date_add('month', +1, date_parse(periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) periodo_camapnia,
          periodo_val periodo_ejecucion
          numruc_val num_documento
          FROM e_perm_aws.T_FACT_VPC_UNIVERSO_BPE 
          WHERE 1 = 1
          AND periodo_val = (SELECT MAX(periodo_val) FROM e_perm_aws.T_FACT_VPC_UNIVERSO_BPE)
          AND coalesce(Flg_baja, 0) = 0
          AND coalesce(flg_Baja_Estado, 0) = 0
          AND coalesce(Flg_Baja_Estado_Oficio, 0) = 0
          AND numruc_val is not null
          GROUP BY numruc_val
    ) PRESENTE
    LEFT JOIN e_perm_aws.t_fact_vpc_tlv_feedback P1 
    ON PRESENTE.num_documento = P1.num_documento AND PRESENTE.periodo_val = substr(replace(substr(cast(date_add('month', +1, date_parse(P1.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
    LEFT JOIN e_perm_aws.t_fact_vpc_tlv_feedback P2 
    ON PRESENTE.num_documento = P2.num_documento AND PRESENTE.periodo_val = substr(replace(substr(cast(date_add('month', +2, date_parse(P2.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
    LEFT JOIN e_perm_aws.t_fact_vpc_tlv_feedback P3 
    ON PRESENTE.num_documento = P3.num_documento AND PRESENTE.periodo_val = substr(replace(substr(cast(date_add('month', +3, date_parse(P3.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
) AA
LEFT JOIN e_perm_aws.t_fact_vpc_agg_sunat_reniec SS  ---- SE VOLVIO A CORRER EL 3 DE AGOSTO CON d_perm_aws x una observacion
ON AA.periodo_ejecucion = SS.periodo_val
   AND AA.num_documento = SS.numruc_val
GROUP BY AA.periodo_campania
        ,AA.periodo_ejecucion
        ,AA.num_documento
)