SELECT DISTINCT

date_trunc('day', block_timestamp) as day,

pool_name,

last_value(tp0.price) over (partition by day,pool_name order by day) as price0,

last_value(tp1.price) over (partition by day,pool_name order by day) as price1,

last_value(token0_balance_adjusted) over (partition by day,pool_name order by day) as balance0,

last_value(token1_balance_adjusted) over (partition by day,pool_name order by day) as balance1,

(price0 * balance0) + (price1 * balance1) as liquidity

FROM uniswapv3.pool_stats

INNER JOIN ethereum.token_prices_hourly tp0 on tp0.hour = date_trunc('day',block_timestamp) AND

token0_address = tp0.token_address

INNER JOIN ethereum.token_prices_hourly tp1 on tp1.hour = date_trunc('day',block_timestamp) AND

token1_address = tp1.token_address

WHERE block_timestamp > getdate() - interval '1 day'

HAVING day = '2021-06-05'

ORDER BY liquidity DESC

LIMIT 5)

day,

pool_name,

liquidity

FROM d

LIMIT 5