drop table if exists xiangsu.new_account_recharge;
create view xiangsu.new_account_recharge as  (select date,
       `#os`,
       `#bundle_id`,
       owner_name,
       channel,
       uniqExact(binduid) as accout_num,
       sum(money) as money
from (select toDate(addHours(`#event_time`, `#zone_offset`)) as date,
             `#os`,
             `#bundle_id`,
             owner_name,
             channel,
             binduid,
             money
      from xiangsu.event
      where `#event_name` = 'rechargeGame') as tb1
         right join (select toDate(addHours(`#event_time`, `#zone_offset`)) as date,
                           binduid
                    from xiangsu.event
                    where role_idx = 1) as tb2
                   on tb1.date = tb2.date and tb2.binduid = tb1.binduid
group by date, `#os`, `#bundle_id`, owner_name, channel)