grahamCoinbase Net Token Flows
Updated 2023-10-16
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
27
28
29
30
31
32
33
34
35
36
›
⌄
with ETH_token_base as (
select
block_timestamp::date as date,
c.address_name,
-- b.symbol as token,
case when lower(b.symbol) in ('usdc', 'busd', 'usdt', 'dai', 'bnb', 'weth', 'wbtc') then b.symbol
else 'other' end as token,
sum(raw_amount / pow(10,decimals)*price) as amt_usd
from ethereum.core.fact_token_transfers a
left join ethereum.price.ez_hourly_token_prices b
on a.contract_address = b.token_address and b.hour = date_trunc('hour', a.block_timestamp)
inner join (select distinct address, address_name from crosschain.core.dim_labels
where label_subtype = 'hot_wallet' and project_name like 'coinbase'
and address_name like 'coinbase %' and blockchain = 'ethereum') c
on a.from_address = c.address
where a.block_timestamp >= current_date - {{lookback_days}}
and a.to_address not in (select distinct address from crosschain.core.dim_labels
where label_subtype = 'hot_wallet' and project_name like 'coinbase'
and address_name like 'coinbase %' and blockchain = 'ethereum')
-- and lower(b.symbol) in ('usdc', 'busd', 'bnb', 'weth')
group by 1,2,3
),
net_out as (
select
distinct date,
address_name,
token,
-amt_usd ,
'out' as direction
from ETH_token_base
),
ETH_token_base_in as (
select
block_timestamp::date as date,
c.address_name,
-- b.symbol as token,
Run a query to Download Data