adriaparcerisasinflows and outflows redux 2
Updated 2022-11-02
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_to_opt as (
select
trunc(block_timestamp,'week') as date,
count(distinct origin_from_address) as users,
count(distinct tx_hash) as txs,
sum(amount) as amount
from ethereum.core.ez_token_transfers
where symbol='USDT' and origin_to_address = lower('0x99C9fc46f92E8a1c0deC1b1747d010903E884bE1')
group by 1
),
opt_to_eth as (
select
trunc(block_timestamp,'week') as date,
count(distinct origin_from_address) as users,
count(distinct tx_hash) as txs,
sum(raw_amount/pow(10,decimals)) as amount
from optimism.core.fact_token_transfers x
join optimism.core.dim_contracts y on x.contract_address=y.address
where origin_to_address = '0x4200000000000000000000000000000000000010'
and symbol='USDT'
group by 1
)
SELECT
x.date,
ifnull(x.users,0) as users_in,
ifnull(x.txs,0) as txs_in,
ifnull(x.amount,0) as amt_in,
ifnull(y.users*(-1),0) as users_out,
ifnull(y.txs*(-1),0) as txs_out,
ifnull(y.amount*(-1),0) as amt_out,
users_in+users_out as net_users,
txs_in+txs_out as net_txs,
amt_in+amt_out as net_volume,
sum(net_volume) over (order by x.date) as cum_net_volume
from eth_to_opt x
Run a query to Download Data