with
transfer_from_cte as (
select
BLOCK_TIMESTAMP::date date
, ETH_TO_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_FROM_ADDRESS = b.address and b.label_type = 'cex'
left join ethereum.core.dim_labels c on a.ETH_TO_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-09-15' then 'Before'
when date > '2022-09-15' then 'After'
else 'The Merge'
end period
, sum(amount) amount
, sum(tx_count) tx_count
, count(distinct sender) sender
from transfer_from_cte
group by 1,2
)
where 1=1
and date between '2022-09-01' and '2022-10-30'