Enviado a Luz Ormeño

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

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

-------TABLA 3
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
        ) 

---------TABLA 4
SELECT periodo, cdounico, 
       max(flg_activo) flg_activo, 
       max(flg_pasivo) flg_pasivo,
       max(FLG_MES_PREVIO) FLG_MES_PREVIO,
       max(flg_cart_base) flg_cart_base,
       periodo p_periodo
FROM (
      SELECT periodo, cdounico, 0 flg_activo, 0 flg_pasivo, 0 FLG_MES_PREVIO, 0 flg_cart_base
      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, 0 FLG_MES_PREVIO,0 flg_cart_base
      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, 0 FLG_MES_PREVIO,0 flg_cart_base
      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)
      UNION
      SELECT '{periodo_ejecucion}' periodo,cuc_num cdounico, 0 flg_activo, 0 flg_pasivo,1 FLG_MES_PREVIO,COALESCE(flg_cart_base,0) flg_cart_base
      FROM disc_comercial.HM_SEG_BPE_CARTERA_MENSUAL
      WHERE CAST(periodo_ejecucion AS VARCHAR) = substr(replace(substr(cast(date_add('month', -1, date_parse('{periodo_ejecucion}' ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
)
GROUP BY periodo, cdounico
ORDER BY periodo DESC, cdounico


-----------TABLA 5
SELECT periodo, 
       cdounico, 
       max(flg_activo) flg_activo, 
       max(flg_pasivo) flg_pasivo,
       max(FLG_MES_PREVIO) FLG_MES_PREVIO,
       MAX(flg_cart_base) flg_cart_base,
       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,XX.FLG_MES_PREVIO,XX.flg_cart_base,
           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


----------TABLA 6
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 4 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.FLG_MES_PREVIO) FLG_MES_PREVIO,
       MAX(XX.flg_cart_base) flg_cart_base,
       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