banbannardUntitled Query
Updated 2022-06-25Copy 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
30
31
32
33
34
35
36
›
⌄
with uniswapv2 as (select
date_trunc('day', block_timestamp) as day,
coalesce(sum(AMOUNT_OUT_USD),0) as volume_univ2,
volume_univ2 * 0.003 as fees_collected_univ2
from ethereum.core.ez_dex_swaps
where platform = 'uniswap-v2'
and AMOUNT_OUT_USD > 0
and day >= '2022-06-15'
and AMOUNT_OUT_USD < 999999999
group by 1),
uniswapv3 as (select
date_trunc('day', block_timestamp) as day,
coalesce(sum(AMOUNT_OUT_USD),0) as volume_univ3,
volume_univ3 * 0.003 as fees_collected_univ3
from ethereum.core.ez_dex_swaps
where platform = 'uniswap-v3'
and AMOUNT_OUT_USD > 0
and day >= '2022-06-15'
and AMOUNT_OUT_USD < 999999999
group by 1),
uni_price as (select date_trunc('day', hour) as day,
avg(price) as price_uni
from ethereum.token_prices_hourly
where token_address ilike '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984'
and day >= '2022-06-15'
group by 1)
select a.day,
price_uni,
volume_univ2 + volume_univ3 as total_uniswap_vol,
volume_univ2,
sum(volume_univ2) over (order by a.day) as cumulative_volume_uniswap_v2,
volume_univ3,
fees_collected_univ2,
Run a query to Download Data