roketETH 3
Updated 2022-09-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
›
⌄
with cex_address as (select address , project_name, blockchain
from flipside_prod_db.crosschain.address_labels
where label_type = 'cex' and label_subtype = 'hot_wallet'
and blockchain in ('ethereum')
)
,
ethereum as ( select date(block_timestamp) as date, tx_hash, project_name , to_address as user, symbol, amount_usd
from ethereum.core.ez_token_transfers a join cex_address b on a.from_address = b.address
where blockchain = 'ethereum' and block_timestamp::date between '2022-09-01' and '2022-09-30' and to_address not in ( select address from cex_address)
)
select 'Ethereum' as chain ,
date,
case
when date < '2022-09-15' then 'before-merge'
when date >= '2022-09-15' then 'after-merge'
end as "type",
count(DISTINCT(tx_hash)) as transfer,
count(DISTINCT(user)) as users,
sum(amount_usd) as volume,
sum(transfer) over (order by date asc) as cum_tx,
sum(volume) over (order by date asc) as cum_volume
from ethereum
group by 1,2
Run a query to Download Data