theericstoneMost Efficient Pools
Updated 2023-03-27
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
›
⌄
with tvl as (
select
DISTINCT
date_trunc('day',block_timestamp) as day,
pool_name,
last_value(token0_balance_usd + token1_balance_usd) OVER (partition by pool_address, pool_name ORDER BY day) as tvl
from uniswapv3.pool_stats
where block_timestamp > getdate() - interval '2 days'
),
swaps as (
select
date_trunc('day',block_timestamp) as day,
pool_name,
sum(abs(amount0_usd)) as trade_volume_usd
from uniswapv3.swaps
where block_timestamp > getdate() - interval '2 days'
group by 1,2
)
SELECT pool_name, tvl, swap_volume as volume, capital_efficiency FROM (
select
tvl.day,
swaps.pool_name,
sum(tvl.tvl) as tvl,
sum(swaps.trade_volume_usd) as swap_volume,
sum(swaps.trade_volume_usd)/sum(tvl.tvl) as capital_efficiency
from tvl left join swaps on tvl.pool_name = swaps.pool_name and tvl.day = swaps.day
where tvl IS NOT NULL and trade_volume_usd IS NOT NULL
group by 1,2)
WHERE swap_volume > 2000000 and day = (select max(day) from tvl)
order by capital_efficiency desc;
Run a query to Download Data