SELECT date_time, pool_address as pool_address1, pool_name, price_status,

CASE WHEN price_status = 'both prices' THEN token0_gross_usd + token1_gross_usd

WHEN price_status = 'price1' THEN token1_gross_usd + ((gross_bal_adj_0 * native_price1) *token_price_1)

WHEN price_status = 'price0' THEN token0_gross_usd + ((gross_bal_adj_1 * native_price0) *token_price_0)

ELSE NULL END AS pool_tvl_in_usd

FROM (

SELECT DISTINCT date_trunc('hour',ps.block_timestamp) as date_time, pool_name, ps.pool_address,

last_value( VIRTUAL_RESERVES_TOKEN0_ADJUSTED * pow(VIRTUAL_RESERVES_TOKEN1_ADJUSTED,-1) ) OVER (PARTITION BY date_time, pool_address, pool_name ORDER BY block_timestamp) as native_price0,

last_value( VIRTUAL_RESERVES_TOKEN1_ADJUSTED * pow(VIRTUAL_RESERVES_TOKEN0_ADJUSTED,-1) ) OVER (PARTITION BY date_time, pool_address, pool_name ORDER BY block_timestamp) as native_price1,

last_value(token_price0.price) OVER (PARTITION BY date_time, pool_address, pool_name ORDER BY block_timestamp) as token_price_0,

last_value(token_price1.price) OVER (PARTITION BY date_time, pool_address, pool_name ORDER BY block_timestamp) as token_price_1,

last_value(token0_balance_adjusted) OVER (PARTITION BY date_time, pool_address, pool_name ORDER BY block_timestamp) as gross_bal_adj_0,

last_value(token1_balance_adjusted) OVER (PARTITION BY date_time, pool_address, pool_name ORDER BY block_timestamp) as gross_bal_adj_1,

token_price_0 * gross_bal_adj_0 as token0_gross_usd,token_price_1 * gross_bal_adj_1 as token1_gross_usd,

CASE WHEN token_price_0 IS NULL and token_price_1 IS NULL THEN 'no prices'

WHEN token_price_0 IS NULL and token_price_1 IS NOT NULL THEN 'price1'

WHEN token_price_1 IS NULL and token_price_0 IS NOT NULL THEN 'price0'

ELSE 'both prices'

END AS price_status

FROM uniswapv3.pool_stats ps LEFT JOIN ethereum.token_prices_hourly token_price0

ON token_price0.hour = date_trunc('hour',ps.block_timestamp) AND ps.token0_address = token_price0.token_address

LEFT JOIN ethereum.token_prices_hourly token_price1 ON token_price1.hour = date_trunc('hour',ps.block_timestamp)

AND ps.token1_address = token_price1.token_address

WHERE ps.block_timestamp >= dateadd(DAY, -7, getdate()) ORDER BY date_time DESC, ps.pool_address

) WHERE price_status <> 'no prices'

),

final_pool_stats as (

select * from uniswapv3.swaps where pool_address in (select pool_address1 from

(

SELECT pool_address1, pool_name, pool_tvl_in_usd as gross_liquidity FROM gross_pool_stats WHERE pool_tvl_in_usd <> 'NaN'

AND date_time = (select max(date_time) from gross_pool_stats) ORDER BY gross_liquidity DESC LIMIT 5

)

)

select date_trunc('day', block_timestamp) AS date, pool_name, avg(AMOUNT0_USD + AMOUNT1_USD) as avg_amount_usd,