select

POOL_ADDRESS,

avg(token0_balance_usd + token1_balance_usd) as TVL

from ethereum.uniswapv3.ez_pool_stats

where date_trunc('day',block_timestamp) = CURRENT_DATE - 1

and token0_balance_usd is not null and token1_balance_usd is not null

and pool_name is not null

group by POOL_ADDRESS

ORDER BY TVL DESC

LIMIT 150

),

historical_tvl AS (

select

date_trunc('day',block_timestamp) as time,

POOL_ADDRESS,

avg(token0_balance_usd + token1_balance_usd) as TVL

from ethereum.uniswapv3.ez_pool_stats

where POOL_ADDRESS IN (SELECT POOL_ADDRESS FROM lp_ltv_100)

AND date_trunc('day',block_timestamp) >= CURRENT_DATE - 31

group by POOL_ADDRESS, time

),

swaps as (

select

date_trunc('day',s.block_timestamp) as time,

POOL_ADDRESS,

count(distinct(s.TX_HASH)) as n_swaps,

count(distinct(FROM_ADDRESS)) as n_swappers,

SUM(IFF(abs(AMOUNT0_USD) > pow(10,9), abs(AMOUNT0_USD)/pow(10,18), abs(AMOUNT0_USD))) as swap_volume

from ethereum.uniswapv3.ez_swaps s

LEFT JOIN ethereum.core.fact_transactions t ON t.TX_HASH = s.TX_HASH

WHERE (s.BLOCK_TIMESTAMP) >= CURRENT_DATE - 31

AND POOL_ADDRESS IN (SELECT POOL_ADDRESS FROM lp_ltv_100)

group by POOL_ADDRESS, time