SELECT

symbol,

price

FROM osmosis.price.ez_prices

WHERE symbol IN ('WETH', 'SEI')

qualify row_number () over (partition by symbol ORDER by RECORDED_HOUR desc) = 1

SELECT

case when pool_name = 'Levana Perps Market - SEI_USD' then 'SEI-USD'

when pool_name = 'Levana Perps Market - ETH_USD' then 'ETH-USD' else pool_name end as pool_name,

x.symbol,

n_txns,

n_user,

add_value,

remove_value,

net as liquidity,

net * price as liquidity_usd

FROM (

SELECT

pool_name,

b.symbol,

count(DISTINCT tx_id) as n_txns,

count(DISTINCT LIQUIDITY_PROVIDER_ADDRESS) as n_user,

sum(case when lp_action = 'add_liquidity' then token1_amount/pow(10,b.decimals) else 0 end) as add_value,

sum(case when lp_action = 'remove_liquidity' then token1_amount/pow(10,b.decimals) else 0 end ) as remove_value,

add_value - remove_value as net

FROM sei.defi.fact_lp_actions a

JOIN sei.core.dim_tokens b ON a.token1_currency = b.currency

WHERE platform = 'levana'

GROUP BY 1 , 2

) x

LEFT JOIN token_price y ON x.symbol = y.symbol

ORDER BY 1