数据需求1-提取存量低活跃客户数据

1、低aum客户：季日均金融资产在1000元以下

2、有效以下低频交易客户：季日均金融资产1000-20000元，但季交易笔数不足3笔。

机构号、机构名、低aum客户、有效以下低频交易客户

(select a.host_cust_id,a.'金融资产季日均' from(

(select host_cust_id,sum(fin_asset_bal_quar_day_avg)'金融资产季日均'

from mdb_bej.b88_cust_fin_asset_info

where stat_dt = '20200731'

and fin_asset_cd in ('100','120','130','140','170','180')

group by host_cust_id

having '金融资产季日均' between 1000 and 20000)a

inner join

(select host_cust_id,late_3mon_tx_cnt

from appview_bs.t88_rtl_cust_syn_info

where stat_dt = '20200731'

and camp_recmd_level1_brch_org_num = '9A01'

and late_3mon_tx_cnt<3)b

on a.host_cust_id = b.host_cust_id))

union

(select host_cust_id,sum(fin_asset_bal_quar_day_avg)'金融资产季日均'

from mdb_bej.b88_cust_fin_asset_info

where stat_dt = '20200731'

and level1_brch_org_num = '9A01'

and fin_asset_cd in ('100','120','130','140','170','180')

group by host_cust_id

having '金融资产季日均'<1000)

 

 

sel kh_jgdm,sum(low_aum_cnt),sum(valid_low_cnt)
from
(sel kh_jgdm
case when jrzc_quar<=1000 then 1 else 0 end low_aum_cnt,
case when jrzc_quar>1000 and jrzc_quar<=20000 and trans_cnt<=3 then 1 else 0 end valid_low_cnt
from
(sel host_cust_id,camp_belong_org_num from mdb_bej.b88_rtl_cust_basic_info_sum
)a
left join
(sel host_xust_id,
sum(case when fin_asset_cd in ('100','120','130','140','170','180')
then fin_asset_bal_quar_dat_avg end)as jrzc_quar
from appview_bs.t88_rtl_cust_fin_asset_info
where stat_dt in ('20190618') and level_brch_org_num = '9A01'
group by host_cust_id)b
on a.host_cust_id =b.host_cust_id
left join
(sel party_id,count(party_id) trans_cnt
from mdb_br.si2_v_mds_trans
where tx_dt between '20190318' and '20190618'
group by party_id)c
on a.host_cust_id = c.party_id
left_join
(sel ztdm,rydm from appview_bs.sg8_sjjz_ryfc_sa
where sldm = '101' and ztlx = '66'
and end_dt = '30001231' and del_ind<>'D')A1
on a.host_xust_id= a1.ztdm
left join
(sel rydm,xm,kh_jgdm from appview_bs.sg8_sjjz_ry
where sldm ='101' and end_dt = '30001231'
)B1
on a1.rtdm=b1.rydm
where jrzc_quar<=20000)s
group by kh_jgdm