rajsWhite Eth Price
Updated 2022-08-29Copy Reference Fork
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
›
⌄
with prices as
(
SELECT
*
from ethereum.core.fact_hourly_token_prices
where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
-- order by 1 desc
-- limit 10
)
SELECT
-- distinct symbol_in,
-- distinct symbol_in
date_trunc('hour', block_timestamp) as date,
-- sum(case when symbol_in = 'WETH' then amount_in * price end) as buy_amt_usd,
-- sum(case when symbol_out = 'WETH' then amount_out * price end) as sell_amt_usd
avg(case when symbol_in = 'WETH' then amount_in * p.price / amount_out * 1000000
when symbol_out = 'WETH' then amount_out * p.price / amount_in * 1000000 end) as white_price,
avg(p.price) as eth_price
-- case when symbol_in = 'WETH' then amount_in * price / amount_out else amount_out * price / amount_in end as price,
-- *
from ethereum.core.ez_dex_swaps s
left join prices p
on date_trunc('hour', s.block_timestamp) = p.hour
where contract_address = '0xa51f018a6c9815cd6756d2b2ddf1bac9d003149d'
-- where token_in = '0xfe4beb9217cddf2422d4bd65449b76d807b30fe1'
group by 1
order by 1 desc
-- limit 100
Run a query to Download Data