info as (

select

tx_id,

block_timestamp,

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

else 'stOSMO' 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,6) 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/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC','uosmo')

--weth,wbtc

-- limit 5

--and msg_type='token_swapped'

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

trunc(block_timestamp,'day') as date,

currency,

actions,

sum(amount) as daily_volume