with 'Android' as os,
    'gmhdtt' as owner,
    '2021-07-11' as start_date,
    '2021-07-20' as end_date
select date,
       `#bundle_id`,
       active_num,
       new_account_num,
       new_account_array,
       money,
       recharge_account_num,
       round(recharge_account_num * 100 / active_num, 2)      as pay_rate,
       round(money / recharge_account_num, 2)                 as arppu,
       round(money / active_num, 2)                           as arpu,
       arrayMap(x-> x.2, arrayFilter(x->has(new_account_array, x.1), bid_money))                       as new_recharge,
       arraySum(new_recharge) as new_money,
       length(new_recharge) as new_pay_num,
       round(new_pay_num*100/new_account_num,2) as new_pay_rate,
       round(new_money*100/new_pay_num,2) as new_arppu,
       round(new_money*100/new_account_num,2) as new_arpu
from (select date,
             `#bundle_id`,
             active_num,
             new_account_num,
             new_account_array,
             money,
             recharge_account_num
      from (select date, `#bundle_id`, active_num, new_account_num, new_account_array
            from (select date, `#bundle_id`, sum(num) as active_num
                  from zhengba.active_account
                  where date >= start_date
                    and date <= end_date
                    and `#os` = os
                    and owner_name = owner
                  group by date, `#bundle_id`) as active_tbl
                     left join (select date,
                                       `#bundle_id`,
                                       sum(num)                     as new_account_num,
                                       flatten(groupArray(account)) as new_account_array
                                from zhengba.new_account
                                where date >= start_date
                                  and date <= end_date
                                  and `#os` = os
                                  and owner_name = owner
                                group by date, `#bundle_id`) as new_account_tbl
                               on active_tbl.date = new_account_tbl.date and
                                  active_tbl.`#bundle_id` = new_account_tbl.`#bundle_id`) as tb1
               left join (select date, `#bundle_id`, sum(money) as money, sum(account_num) as recharge_account_num
                          from zhengba.recharge_game
                          where date >= start_date
                            and date <= end_date
                            and `#os` = os
                            and owner_name = owner
                          group by date, `#bundle_id`) as recharge_tbl
                         on recharge_tbl.date = tb1.date and tb1.`#bundle_id` = recharge_tbl.`#bundle_id`) as tb2
         left join zhengba.new_account_recharge as tb3 on tb2.date = tb3.date
order by date desc