mehrancrypto-dxoepqUntitled Q60-2
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
28
29
30
31
›
⌄
with tx1 as (select address , ADDRESS_NAME
from ethereum.core.dim_labels
where label = 'tornado cash'
) ,
tx2 as (
SELECT ADDRESS_NAME , ETH_VALUE , block_timestamp::date as daily , tx_hash
from tx1 A , ethereum.core.fact_transactions B
where address = TO_ADDRESS
and block_timestamp > CURRENT_DATE - 91
) ,
tx3 as
(
SELECT tx_hash , avg(price) as price
from ethereum.core.fact_hourly_token_prices A , tx2
where symbol = 'WETH'
and hour::date = daily
GROUP by 1
)
, tx4 as (
SELECT ADDRESS_NAME , A.tx_hash , price*ETH_VALUE as amount
from tx2 A, tx3 B
where A.tx_hash = B.tx_hash
)
SELECT A.ADDRESS_NAME , sum(amount) as USD , sum(eth_value) ,COUNT(A.tx_hash)
from tx4 A , tx2 B
where A.tx_hash = B.tx_hash
and A.ADDRESS_NAME = B.ADDRESS_NAME
group by 1
Run a query to Download Data