select

tx_id

from osmosis.core.fact_msg_attributes

where msg_type = 'burn'

and attribute_key = 'amount'

and attribute_value like '%/pool/%'

),

pool_exited_uosmo_burn_info as (

select

distinct tx_id,

block_timestamp,

case

when (split(attribute_value, ',')[1] is not null and split(attribute_value, ',')[1] like '%uosmo') then left(split(attribute_value, ',')[1] ,len(split(attribute_value, ',')[1]) - 5)::float

when (split(attribute_value, ',')[1] is null and split(attribute_value, ',')[1] not like '%uosmo' and split(attribute_value, ',')[0] like '%uosmo') then left(split(attribute_value, ',')[0] ,len(split(attribute_value, ',')[0]) - 5)::float

else 0

end as osmo_burn_amount,

'Exit Pool' as burn_type

from osmosis.core.fact_msg_attributes attributes

join pool_exited_list pool_burn using(tx_id)

where msg_type = 'pool_exited' and attribute_key = 'tokens_out' and attribute_value like '%uosmo'

),

ibc_transfer_out_chain_list as (

select

tx_id

from osmosis.core.fact_msg_attributes

where msg_type = 'burn'

and attribute_key = 'amount'

and attribute_value not like '%/pool/%'

),

ibc_transfer_uosmo_burn_info as (

select

distinct tx_id,

block_timestamp,

(left(attribute_value ,len(attribute_value) - 5)::float) as osmo_burn_amount,

'IBC Transfer' as burn_type