mehrancrypto-dxoepqUntitled Q60-6
Updated 2022-08-10
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
›
⌄
with tx1 as (select address , ADDRESS_NAME
from ethereum.core.dim_labels
where label = 'tornado cash'
) , tx2 as (
SELECT ETH_VALUE, ADDRESS_NAME , B.tx_hash , C.amount , D.amount As tokens , CONTRACT_ADDRESS , B.block_timestamp::date as daily
from tx1 A , ethereum.core.fact_transactions B
left outer JOIN ethereum.core.ez_eth_transfers C on B.tx_hash = C.tx_hash
left outer JOIN ethereum.core.ez_token_transfers D on B.tx_hash = D.tx_hash
where address = B.TO_ADDRESS
and B.block_timestamp > CURRENT_DATE - 31
) ,
tx3 as (
SELECT tx_hash , avg(price) as price, symbol
from ethereum.core.fact_hourly_token_prices A , tx2 B
where CONTRACT_ADDRESS = TOKEN_ADDRESS
and hour::date = daily
GROUP by 1 , 3
)
, tx4 as (
SELECT ADDRESS_NAME ,symbol , A.tx_hash , price*tokens as usd_volume
from tx2 A, tx3 B
where A.tx_hash = B.tx_hash
)
SELECT ADDRESS_NAME, sum(usd_volume)
from tx4
group by 1
Run a query to Download Data