-- pool_address,

date_trunc('day',uniswapv3.pool_stats.block_timestamp) as day,

uniswapv3.pool_stats.pool_name as pool_name,

uniswapv3.pools.fee_percent,

avg(virtual_liquidity_adjusted) as virtual_liquidity

FROM uniswapv3.pool_stats

INNER JOIN uniswapv3.pools on uniswapv3.pool_stats.pool_name = uniswapv3.pools.pool_name

-- (uniswapv3.pool_stats.pool_name LIKE 'DAI-WETH%' OR uniswapv3.pool_stats.pool_name LIKE 'USDC-WETH%'

-- OR uniswapv3.pool_stats.pool_name LIKE 'WETH-USDT%' OR uniswapv3.pool_stats.pool_name LIKE 'UNI-WETH%')

-- AND

-- uniswapv3.pool_stats.block_timestamp >= getdate() - interval '1 week'

-- AND uniswapv3.swaps.block_timestamp >= getdate() - interval '1 week'

GROUP BY day,uniswapv3.pool_stats.pool_name, uniswapv3.pools.fee_percent

ORDER BY day),

r as (

SELECT h.pool_name as pool_name, last_value(virtual_liquidity) OVER (partition by pool_name order by day) as virtual_liquidity_usd

FROM h

),

g as (SELECT

SUM(CASE WHEN pool_name LIKE '%500 10' THEN virtual_liquidity_usd END) as five_per,

SUM(CASE WHEN pool_name LIKE '%3000 60' THEN virtual_liquidity_usd END) as sixty_per,

SUM(CASE WHEN pool_name LIKE '%10000 200' THEN virtual_liquidity_usd END) as hundo_per

FROM r)

SELECT * FROM

(

SELECT

ROUND(five_per/(five_per+sixty_per+hundo_per)*100,2) as low_fees, --ZERO_POINT_ZERO_FIVE_PERCENT,

ROUND(sixty_per/(five_per+sixty_per+hundo_per)*100,2) as mid_fees, --ZERO_POINT_SIX_PERCENT,

ROUND(hundo_per/(five_per+sixty_per+hundo_per)*100,2) as high_fees--ONE_PERCENT

FROM g

)