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 >= '2023-01-01' then 'transfer from'

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 '2023-01-01' and '2023-02-12'