adriaparcerisaseth down 4
Updated 2022-11-23
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
›
⌄
with
t1 as (
select trunc(block_timestamp,'day') as date,
sum(amount_in_usd) as volume_sold
from ethereum.core.ez_dex_swaps
where symbol_in in ('ETH','WETH') and block_timestamp>=current_date - 30 and amount_in_usd<1e9
group by 1--,2
),
t2 as (
select trunc(block_timestamp,'day') as date,
sum(amount_out_usd) as volume_bought
from ethereum.core.ez_dex_swaps
where symbol_out in ('ETH','WETH') and block_timestamp>=current_date - 30 and amount_out_usd<1e9
group by 1--,2
),
t3 as (
select
trunc(hour,'day') as date,
avg(price) as eth_price,
LAG(eth_price,1) IGNORE NULLS OVER (ORDER BY date) as last_price,
((eth_price-last_price)/eth_price)*100 as eth_price_change
from ethereum.core.fact_hourly_token_prices where symbol='WETH' and hour>=current_date - 30
group by 1
),
t4 as (
select
t1.date,
volume_bought,volume_sold*(-1) as volume_solds,
volume_bought-volume_sold as net_volume,eth_price,eth_price_change
from t1
join t2 on t1.date=t2.date
join t3 on t1.date=t3.date
)
select * from t4
order by 1 asc
Run a query to Download Data