telecom_usecase_2.sql

select A.ban_num, 
(case when B.NUM_SUBS is not null then (A.ARPU/B.NUM_SUBS) else A.ARPU end) as avg_arpu 
from
(select ban_num, round(sum(nvl(cur_rc_amt,0) + nvl(discount_amt,0) + nvl(cur_uc_amt,0) + nvl(cur_oc_amt,0)),2) 
as ARPU
from xxxx.FCT_INVOICE t where cycle_mth in ('03') and cycle_year in ('2021')
group by ban_num) A
left join 
(select a.ban_num, count(a.msisdn) as num_subs
from xxxx.DIM_SUBSCRIBER a 
where (a.subs_deactv_tm_key_day is null or a.subs_deactv_tm_key_day > '20210330' )
and (a.subs_actv_tm_key_day <= '20210330')
and a.dwh_subs_status not in ('B') and a.subs_type in ('POST') #'PREP'
group by a.ban_num) B
on (A.BAN_NUM=B.BAN_NUM)