jqb44077Untitled Query
Updated 2022-03-07
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 daily as (select
date_trunc('day',block_timestamp) as day,
pool_name,
pool_address as pool_pyco,
avg(price_0_1) as avg_price_0,
min(price_0_1) as min_price_0,
max(price_0_1) as max_price_0,
avg(price_1_0) as avg_price_1,
min(price_1_0) as min_price_1,
max(price_1_0) as max_price_1,
sum(abs(amount0_usd)*.5+abs(amount1_usd)*.5) as volume
from uniswapv3.swaps
where day > dateadd('day',-300,CAST('2022-02-28 23:00:00.000' as DATETIME)) and day <= '2022-02-28 23:00:00.000'
group by 1,2,3),
pools as (
select fee_percent,
date_trunc('day',block_timestamp) as daya,
pool_name as fee_pool_name,
pool_address as adress
from uniswapv3.positions
where daya > dateadd('day',-300,CAST('2022-02-28 23:00:00.000' as DATETIME)) and daya <= '2022-02-28 23:00:00.000'
group by 1,2,3,4),
tvl as (select
date_trunc('day',block_timestamp) as tvl_day,
pool_name as tvl_pool_name,
avg(token0_balance_usd + token1_balance_usd) as avg_tvl
from uniswapv3.pool_stats
where tvl_day > dateadd('day',-300,CAST('2022-02-28 23:00:00.000' as DATETIME)) and tvl_day <= '2022-02-28 23:00:00.000'
group by 1,2),
merged_daily as (
select * from daily
join tvl on pool_name = tvl_pool_name and day = tvl_day
inner join pools on pool_pyco = adress and day = daya
Run a query to Download Data