我会先尝试横向连接：

select cm.*,
       coalesce(pi.carryover_amount, cm.carryover_amount)
from customer_master cm left join lateral
     (select pi.*
      from payment_info pi
      where pi.customer_id = cm.customer_id and
            pi.created_date < to_date('202007' || '01', 'YYYYMMDD') + interval '1 month'
      order by pi.created_date < to_date('202007' || '01', 'YYYYMMDD') desc,
               (case when pi.created_date < to_date('202007' || '01', 'YYYYMMDD') then pi.created_date end) desc,
               pi.created_date asc
      limit 1
     ) pi
     on true;

当我们专门为大数据集构建查询时，在select语句中使用select语句是不好的做法。请尽量避免。
我假设创建日期也有时间。你可以根据你的要求改变。下面是我将如何做到这一点的 backbone 。我希望这就是你想要的：

With tmp AS(Select carrover_amount, customer_id from 
(Select carrover_amount, customer_id from payment_info 
where to_char(created_date, 'YYYYMM') < '202007' 
order by created_date desc) where row_num = 1)

,tmp2 AS(Select CASE WHEN category = 1 THEN carryover_amount - payment_amount
                ELSE carryover_amount + payment_amount END carrover_amount2, customer_id from (Select customer_id, carrover_amount, min(created_date) OVER() as first_dt, payment_amount, created_date from payment_info where to_char(created_date, 'YYYYMM') = '202007' 
order by created_date) where first_dt = created_date)

Select CASE WHEN t1.customer_id IS NOT NULL THEN t1.carrover_amount
            WHEN t2.customer_id IS NOT NULL THEN t2.carrover_amount2
            ELSE b.carryover_amount END carryover
from payment_info a LEFT OUTER JOIN tmp t1 ON a.customer_id = t1.customer_id
                    LEFT OUTER JOIN tmp2 t2 ON a.customer_id = t2.customer_id
                    LEFT OUTER JOIN customer_tbl b ON a.customer_id = b.customer_id;