theericstoneMost Efficient Pools
    Updated 2023-03-27
    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