transfer_to_cte as (

select

BLOCK_TIMESTAMP::date date

, ORIGIN_FROM_ADDRESS sender

, sum(amount) amount

, count(tx_hash) tx_count

from ethereum.core.ez_eth_transfers a

join ethereum.core.dim_labels b on a.ETH_TO_ADDRESS = b.address and b.label_type = 'cex'

left join ethereum.core.dim_labels c on a.ORIGIN_FROM_ADDRESS = c.address

where 1=1

and c.address is null -- sender is not labeled

group by 1,2

)

select

date

, period

, amount

, sender

, tx_count / sender tx_count_per_sender

from (

select

date

, case

when date < '2022-11-08' then 'Before'

when date > '2022-11-08' then 'After'

else 'collapse'

end period

, sum(amount) amount

, sum(tx_count) tx_count

, count(distinct sender) sender

from transfer_to_cte

group by 1,2

)

where 1=1

and date between '2022-10-08' and '2022-12-08'