date_trunc('day',block_timestamp) as date,

avg(price_usd) AS price

FROM (select

block_timestamp,

case

when from_currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7'

and to_currency = 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858'

then (to_amount / POW(10, to_decimal)) / (from_amount / POW(10, from_decimal))

when to_currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7'

and from_currency = 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858'

then (from_amount / POW(10, from_decimal)) / (to_amount / POW(10, to_decimal))

END as price_usd

FROM osmosis.core.fact_swaps

WHERE from_amount > 0

AND to_amount > 0

)

GROUP BY 1

), main as(select

'Deposits' AS "Action",

block_timestamp,

tx_id,

liquidity_provider_address,

amount/pow(10,IFNULL(decimal, 10))* price AS vol

FROM osmosis.core.fact_liquidity_provider_actions a JOIN price1 b ON date_trunc('day',a.block_timestamp) = b.date

where tx_status = 'SUCCEEDED'

and currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7'

and pool_id[0] = 773

and action = 'pool_joined'

group by 1,2,3,4,5

union

'Withdrawals' AS "Action",

block_timestamp,

tx_id,

liquidity_provider_address,