cypher3. ETH Positive Price Action - uniswap pool balances
Updated 2023-01-20
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
›
⌄
-- select * from ethereum.core.fact_dex_pool_daily_reads r
-- join ethereum.core.dim_contracts d on d.address = r.contract_address
-- where date >= '2023-1-1'
-- and date <= '2023-1-16'
-- limit 100
with pool_balances as (select
date_trunc('{{date_aggregation}}', block_timestamp) as date,
pool_name,
avg(token0_balance_usd+token1_balance_usd) as pool_balance_usd
from ethereum.uniswapv3.ez_pool_stats
where date >= '2023-1-1'
and date <= '2023-1-16'
and token0_balance_usd+token1_balance_usd > 1000
group by date, pool_name),
final as (select
date,
sum(pool_balance_usd) as total_balance,
lead(total_balance) over (order by date desc) as previous_balance,
total_balance - previous_balance as net_change
from pool_balances
group by date
),
eth_price as (select
date_trunc('{{date_aggregation}}', hour) as date,
avg(price) as price
from ethereum.core.fact_hourly_token_prices
where token_address is null
and hour >= '2023-1-1'
and hour <= '2023-1-16'
group by date)
select * from final
Run a query to Download Data