info as (

select

tx_id,

block_timestamp,

case when attribute_value like '%uosmo%' then 'uOSMO'

else 'DOT' end as currency,

case when msg_type ='pool_joined' then 'deposit'

when msg_type ='burn' then 'withdraw' end as actions,

case when currency like '%uOSMO%' then

SPLIT_PART(TRIM(REGEXP_REPLACE(

attribute_value,

'[^[:digit:]]',

' ')), ' ', 0)/pow(10,6)

else SPLIT_PART(TRIM(REGEXP_REPLACE(

attribute_value,

'[^[:digit:]]',

' ')), ' ', 0)/pow(10,10) end AS amount

from osmosis.core.fact_msg_attributes

where

--block_timestamp>='2022-06-01' and

msg_type in ('pool_joined','burn')

and

RIGHT(attribute_value, LENGTH(attribute_value) - LENGTH(SPLIT_PART(TRIM(REGEXP_REPLACE(attribute_value, '[^[:digit:]]', ' ')), ' ', 0))) in ('ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7','uosmo')

--weth,wbtc

-- limit 5

--and msg_type='token_swapped'

--and attribute_key in ('tokens_in','tokens_out')

trunc(block_timestamp,'{{granularity}}') as date,

currency,

actions,

sum(amount) as daily_volume

--sum(daily_volume) over (order by date) as cum_volume