DROP TABLE IF EXISTS .......................T_MERCADO_BPE_ADQ_SF_P02
      
      CREATE TABLE .......................T_MERCADO_BPE_ADQ_SF_P02
      WITH ( format = 'Parquet', 
           parquet_compression = 'SNAPPY', 
           partitioned_by = ARRAY['PERIODO'], 
           external_location= 's3://............................................................/T_MERCADO_BPE_ADQ_SF_P02/'
         )
      AS (
        
        SELECT  A.CODSBS,
            A.PRODUCTO,
            A.SITUACION,
            A.COD_EMPRESA,
            A.TIPO_ENTIDAD,
            A.BANCO,
            A.TIPO_PRODUCTO,
            A.FLG_LIN_MERCADO,
            A.MTO_SALDO as MTOSALDO,
            A.MAX_DIAS_ATRASO,
            B.MTOSALDO AS SALDO_REACTIVA,
            C.MTOSALDO AS SALDO_FAE,
            CASE WHEN coalesce(A.MTO_SALDO,0) - coalesce(B.MTOSALDO,0) - coalesce(C.MTOSALDO,0) >= 0 
               THEN coalesce(A.MTO_SALDO,0)- coalesce(B.MTOSALDO,0) - coalesce(C.MTOSALDO,0) ELSE 0 END AS MTOSALDO_REAJUSTADO,
            A.PERIODO
         FROM .......................T_MERCADO_BPE_ADQ_SF_P01 A
         LEFT JOIN
         (
         SELECT PERIODO, CODSBS, COD_EMPRESA, SITUACION, 
            SUM(MTO_SALDO) AS MTOSALDO
         FROM .......................T_MERCADO_BPE_ADQ_SF_P01
         WHERE TIPO_PRODUCTO='REACTIVA' 
         GROUP BY PERIODO, CODSBS, COD_EMPRESA, SITUACION
         ) B ON A.PERIODO = B.PERIODO
           AND A.CODSBS = B.CODSBS
           AND A.COD_EMPRESA = B.COD_EMPRESA
           AND A.SITUACION = B.SITUACION
           AND A.PRODUCTO='PRESTAMOS'
         LEFT JOIN
         (
         SELECT PERIODO, CODSBS, COD_EMPRESA, SITUACION, 
            SUM(MTO_SALDO) AS MTOSALDO
         FROM .......................T_MERCADO_BPE_ADQ_SF_P01
         WHERE TIPO_PRODUCTO='FAE' 
         GROUP BY PERIODO, CODSBS, COD_EMPRESA, SITUACION
         ) C ON A.PERIODO = C.PERIODO
           AND A.CODSBS = C.CODSBS
           AND A.COD_EMPRESA = C.COD_EMPRESA
           AND A.SITUACION = C.SITUACION
           AND A.PRODUCTO='PRESTAMOS'
      )