select

DISTINCT date_trunc('day',pstat.block_timestamp) as block_hour,

pstat.pool_address,

pool_name,

last_value(price_0_1) OVER (partition by block_hour, pool_address, pool_name order by block_timestamp) as price01,

last_value(price_1_0) OVER (partition by block_hour, pool_address, pool_name order by block_timestamp) as price10,

last_value(tph1.price) OVER (partition by block_hour, pool_address, pool_name order by block_timestamp) as price1,

last_value(tph0.price) OVER (partition by block_hour, pool_address, pool_name order by block_timestamp) as price0,

last_value(token0_balance_adjusted) OVER (partition by block_hour, pool_address, pool_name order by block_timestamp) as gross_reserves_token0_adjusted,

last_value(token1_balance_adjusted) OVER (partition by block_hour, pool_address, pool_name order by block_timestamp) as gross_reserves_token1_adjusted,

price0 * gross_reserves_token0_adjusted as token0_gross_usd,

price1 * gross_reserves_token1_adjusted as token1_gross_usd,

case when price0 IS NULL and price1 IS NULL then 'no prices'

when price0 IS NULL and price1 IS NOT NULL then 'price1'

when price1 IS NULL and price0 IS NOT NULL then 'price0' else 'both prices' end as price_status

from uniswapv3.pool_stats pstat

LEFT join ethereum.token_prices_hourly tph0 on tph0.hour = date_trunc('hour',pstat.block_timestamp) AND

pstat.token0_address = tph0.token_address

LEFT join ethereum.token_prices_hourly tph1 on tph1.hour = date_trunc('hour',pstat.block_timestamp) AND

pstat.token1_address = tph1.token_address

where pstat.block_timestamp >= getdate() - interval '31 days' --'2021-05-05 15:00:00'

order by block_hour desc, pstat.pool_address

),

gussied as (

select

block_hour, pool_address,pool_name, price_status, price01, price10, gross_reserves_token0_adjusted, gross_reserves_token1_adjusted,

case when price_status = 'both prices' then token0_gross_usd + token1_gross_usd

when price_status = 'price1' then token1_gross_usd + ((gross_reserves_token0_adjusted * price10) * price1)

when price_status = 'price0' then token0_gross_usd + ((gross_reserves_token1_adjusted * price01) * price0)

else NULL end as tvl_usd

from hourly

where price_status <> 'no prices'

select block_hour,