select

date_trunc('day', recorded_at) as date,

symbol,

avg(price) as price_usd

from osmosis.core.dim_prices

where symbol in ('ATOM', 'OSMO')

group by 1,2

),

lp_txns as (select

CASE pool_id

WHEN '1' THEN 'ATOM / OSMO'

WHEN '678' THEN 'axlUSDC / OSMO'

WHEN '674' THEN 'DAI / OSMO'

WHEN '560' THEN 'USTC / OSMO'

WHEN '633' THEN 'USTC.grv / OSMO'

WHEN '818' THEN 'USDT.grv / OSMO'

ELSE 'OTHER'

END as pool_name,

p.price_usd,

(2 * l.amount * p.price_usd/1e6) as amount_usd,

l.*

from osmosis.core.fact_liquidity_provider_actions l

join prices p on l.block_timestamp::date = p.date

where 1=1

and pool_id in (

'678' -- axlUSDC / OSMO

,'1' -- ATOM / OSMO

,'674' -- DAI / OSMO

,'560' -- USTC / OSMO

,'633' -- USTC.grv / OSMO

,'818' -- USDT.grv / OSMO

)

and action = 'pool_joined'

and currency = 'uosmo'