with pg_tx as ( SELECT account_id, sum(value.amount)/100 as tx_amount FROM `reby-cloud.analytics_reby_v1_eu.pg_transactions_json` where type like '%top_up' and date(created_at) >= date_sub(current_date, INTERVAL 6 MONTH) group by 1 ), account as ( select acc.id, acc.balance__amount/100 as balance_amount, acc.updated_at, tx.account_id, tx.tx_amount from `reby-cloud.analytics_reby_v1_eu.pg_account` acc left join pg_tx tx on acc.id = tx.account_id ), join_user as ( select acc.*, cu.user_id, cu.company_id from account acc left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on acc.id = cu.account_id where acc.account_id is not null and acc.balance_amount > 0 and cu.company_id = 'c_3qteetfhxjshx4j54111' ), join_sa as ( select u.*, fmr.service_area from join_user u left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` fmr on u.user_id = fmr.user_id ) select service_area, sum(balance_amount) as amount from join_sa group by 1 ;