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