with cte_pt as (

select distinct pool_name,

pool_address,

t0.symbol as t0Symbol,

token0,

t0.decimals t0Decimals,

last_value(t0.price) OVER (PARTITION by t0.token_address order by t0.hour desc ) t0Price,

t1.symbol as t1Symbol,

token1,

t1.decimals t1Decimals,

last_value(t1.price) OVER (PARTITION by t1.token_address order by t1.hour desc ) t1Price

from ethereum.dex_liquidity_pools P

join ethereum.token_prices_hourly t0 on lower(t0.token_address) = lower(p.token0) and t0.hour::date = CURRENT_DATE - 1

join ethereum.token_prices_hourly t1 on lower(t1.token_address) = lower(p.token1) and t1.hour::date = CURRENT_DATE - 1

where platform = 'sushiswap'

, cte_pool_usd as (

select

split_part(replace(contract_name,' SLP',''),'-',1) as t0_Sym,

last_value(event_inputs:reserve0::float/POW(10,p.t0Decimals) * p.t0price) OVER (PARTITION by e.contract_Address order by e.block_id desc ) as t0_Amount,

split_part(replace(contract_name,' SLP',''),'-',2) as t1_sym,

last_value(event_inputs:reserve1::float /POW(10,p.t1Decimals) * p.t1price) OVER (PARTITION by e.contract_Address order by e.block_id desc ) as t1_amount,

p.pool_name,

e.contract_address

-- event_inputs:reserve0,

-- t0Decimals,

-- t0price,