(SELECT case when emitter_chain = 'Avax' then '11'

when emitter_chain = 'Oasis' then '12'

when emitter_chain = 'Solana' then '8'

when emitter_chain = 'Ethereum' then '9'

when emitter_chain = 'BSC' then ''

when emitter_chain = 'Polygon' then '10'

when emitter_chain = 'Unknown Chain' then '13' end as source, * from

(SELECT emitter_chain, count(*) as total_failed_txs, '3' as destination, count(DISTINCT sender) as unqiue_participants from

(SELECT msg_value:sender::string as sender,

HEX_ENCODE(BASE64_DECODE_BINARY(msg_value:execute_msg:submit_vaa:data::string)) as vaa_data,

CASE

WHEN vaa_data ILIKE '%ec7372995d5cc8732397fb0ad35c0121e0eaa90d26f828a534cab54391b3a4f5%' THEN 'Solana'

WHEN vaa_data ILIKE '%0000000000000000000000003ee18b2214aff97000d974cf647e7c347e8fa585%' THEN 'Ethereum'

WHEN vaa_data ILIKE '%000000000000000000000000b6f6d86a8f9879a9c87f643768d9efc38c1da6e7%' THEN 'BSC'

WHEN vaa_data ILIKE '%0000000000000000000000005a58505a96d1dbf8df91cb21b54419fc36e93fde%' THEN 'Polygon'

WHEN vaa_data ILIKE '%0000000000000000000000000e082f06ff657d94310cb8ce8b0d9a04541d8052%' or vaa_data ILIKE '%000000000000000000000000f7b6737ca9c4e08ae573f75a97b73d7a813f5de5%' THEN 'Avax'

WHEN vaa_data ILIKE '%00000000000000000000000004952d522ff217f40b5ef3cbf659eca7b952a6c1%' or vaa_data ILIKE '%0000000000000000000000005848c791e09901b40a9ef749f2a6735b418d7564%' THEN 'Oasis'

ELSE 'Unknown Chain'

END as emitter_chain, *

FROM terra.msgs

WHERE msg_value:execute_msg:submit_vaa:data is not null and tx_status != 'SUCCEEDED')

GROUP by 1 )),

wormhole_txs as

(select *,

(msg_value:execute_msg:initiate_transfer:asset:amount)/pow(10,6) as amt ,

SUBSTRING(msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom,0,LEN(msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom)) as denom ,

msg_value:execute_msg:initiate_transfer:recipient_chain as chain_no ,msg_value:sender as sender ,

msg_value:execute_msg:initiate_transfer:asset:info:token:contract_addr as ca from terra.msgs

where msg_value:contract = 'terra10nmmwe8r3g99a9newtqa7a75xfgs2e8z87r2sf' and amt> '0' and tx_status != 'SUCCEEDED'),

starting_chain as

(select chain_no,

case when chain_no = '4' then 'BSC' when chain_no = '2' then 'ETH' when chain_no = '1' then 'SOL' when chain_no = '5' then 'Matic'

when chain_no = '6' then 'avax' when chain_no = '7' then 'oasis'

end as chain_name, '3' as source,

count(*) as total_txs_of_chain,