with pow_buys as ( select eth_to_address as to_address, sum(amount) as buy_amount from ethereum.core.ez_eth_transfers where block_timestamp < '2022-09-15' group by eth_to_address ), pow_sells as ( select eth_from_address as from_address, sum(amount) as sell_amount from ethereum.core.ez_eth_transfers where block_timestamp < '2022-09-15' group by eth_from_address ), pow_assets as ( select b.to_address as address, iff(buy_amount is null, 0, buy_amount)-iff(sell_amount is null, 0, sell_amount) as eth from pow_buys b full outer join pow_sells s on b.to_address=s.from_address ), pos_buys as ( select eth_to_address as to_address, sum(amount) as buy_amount from ethereum.core.ez_eth_transfers group by eth_to_address ), pos_sells as ( select eth_from_address as from_address, sum(amount) as sell_amount from ethereum.core.ez_eth_transfers group by eth_from_address ), pos_assets as ( select b.to_address as address, iff(buy_amount is null, 0, buy_amount)-iff(sell_amount is null, 0, sell_amount) as eth from pos_buys b full outer join pos_sells s on b.to_address=s.from_address ), labled_assets as ( select 'Before Merge' as type, address, eth, case when eth <= 10 then 'A: Eth<=10' when eth > 10 and eth <= 1000 then 'B: 10 1000 and eth <= 10000 then 'C: 1k 10000 and eth <= 100000 then 'D: 10k100k' end as eth_label from pow_assets where eth > 0 union all select 'After Merge' as type, address, eth, case when eth <= 10 then 'A: Eth<=10' when eth > 10 and eth <= 1000 then 'B: 10 1000 and eth <= 10000 then 'C: 1k 10000 and eth <= 100000 then 'D: 10k100k' end as eth_label from pos_assets where eth > 0 ) select type, eth_label, count(distinct address) as address_count, sum(eth) as eth_amount from labled_assets group by type, eth_label order by eth_label asc

Open Analytics Bounty: ETH (October 30)