with msg_list as(

select

tx_id,

block_timestamp,

msg_index,

msg_type,

msg_group,

attribute_value

from cosmos.core.fact_msg_attributes

where

attribute_key = 'amount'

and (

msg_type = 'withdraw_rewards'

or msg_type = 'unbond'

or msg_type = 'delegate'

)

and attribute_value is not null

and attribute_value like '%uatom%'

and block_timestamp >= '2021-12-01'

)

,pruning_msg_list as(

select

tx_id,

block_timestamp,

msg_index,

msg_type,

msg_group,

case

when (attribute_value like '%ibc%') then substr(right(attribute_value, charindex(',', reverse(attribute_value)) -1), 0, position('uatom', right(attribute_value, charindex(',', reverse(attribute_value)) -1))-1)::integer * pow(10,-6)

else substr(attribute_value, 0, position('uatom', attribute_value)-1)::integer * pow(10,-6)

end as amount