info as (

select

tx_id,

block_timestamp,

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

else 'BNB' 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,18) end AS amount

from osmosis.core.fact_msg_attributes

where

block_timestamp>='2022-11-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/F4A070A6D78496D53127EA85C094A9EC87DFC1F36071B8CCDDBD020F933D213D','uosmo')

--weth,wbtc

-- limit 5

--and msg_type='token_swapped'

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

),

deposits as (

trunc(block_timestamp,'day') as date,

--currency,

sum(amount) as daily_volume,