info as (

tx_id,

block_timestamp,

case when attribute_value like '%ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5%' then 'WETH'

else 'WBTC' end as currency,

case when msg_type ='pool_joined' then 'deposit'

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

case when currency like '%WBTC%' then

SPLIT_PART(TRIM(REGEXP_REPLACE(

attribute_value,

'[^[:digit:]]',

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

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-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/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5','ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F')

--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,

sum(daily_volume) over (partition by currency order by date) as cum_volume

from info where actions ='deposit'