(

select

recorded_at::date as "date",

symbol as stars_symbol,

avg(price) as stars_price,

avg(volume_24h) as stars_volume

from osmosis.core.dim_prices

where 1=1

and symbol='STARS'

-- and recorded_at > '2022-09-12'

group by "date",stars_symbol

),

tab as

(

select

lp.*,

case

when action = 'pool_joined' then (lp.amount*p.stars_price/1e6)

when action = 'pool_exited' then (lp.amount*p.stars_price/1e6)*-1

end as usd

from osmosis.core.fact_liquidity_provider_actions lp

inner join stars_price p

on lp.block_timestamp::date=p."date"

where lp.currency='ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4'

and lp.block_timestamp >= '2022-09-26'

)

select

pool_id,

case

when pool_id = '611' then 'stars-atom'

when pool_id = '604' then 'stars-osmo'

when pool_id = '810' then 'stars-stSTARS'

when pool_id = '809' then 'stars-osmo-juno-atom'