我想使用CTE连接主表和事务表。我试着这样做，但我不知道如何利用查询结果加入另一个表。

with CTE_base as (
select
account_number,
orgn_acct,
product_type,
load_date as report_date,
min(concat(case when 
substr(load(cast(acc_open_date as string),6,'0'),1,2)>'30' then '19'else '20' end,
substr(load(cast(acc_open_date as string),6,'0'),1,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),3,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),5,2)))) as open_date
from master
where 
product_type <400 
and product_type not between 290 and 390
and datediff(load_date,concat(case when 
substr(load(cast(acc_open_date as string),6,'0'),1,2)>'30' then '19'else '20' end,
substr(load(cast(acc_open_date as string),6,'0'),1,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),3,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),5,2))) <=398

group by load_date,orgn_acct,product_type,account_number)

select * from CTE_base)master

inner join(
with CTE_fees as (select
trans_code,
march_code,
account_num,
load_date,
case when (
(trans_code =253)
and (march_code =12)
then "annual fee")
end as fee_type)
from transaction)
select * from CTE_fees) fees

on fees.account_num =master.account_number
where datediff(fees.load_date,master.open_date )<=397

如何根据两个表的内部联接结果联接另一个表？我在网上看到，可以使用多个CTE来完成，但不确定
1.如何将两个查询的结果存储在另一个CTE中？