select

RECORDED_HOUR::date as days , SYMBOL,CURRENCY,

avg(price) as prices

from

osmosis.core.ez_prices

group by 1 ,2,3

,fin as (

select

b.*,prices,AMOUNT/pow(10,decimal) as AMOUNTs ,

AMOUNTs*prices as AMOUNT_usd

from

osmosis.core.fact_liquidity_provider_actions b,prices p

where

block_timestamp::date = days and b.CURRENCY = p.CURRENCY

and AMOUNT is not null

select

date_trunc (week,BLOCK_TIMESTAMP)::date as weeks ,

POOL_ID ,

sum(case when action = 'pool_joined' then AMOUNT_usd end ) as deposit,

sum(case when action = 'pool_exited' then AMOUNT_usd end ) as withdraw,

deposit-withdraw as net_usd

from

fin

where action in ('pool_joined','pool_exited')

group by 1,2