select count(distinct a.tx_hash) as tx_num, 'ETHEREUM' AS ORIGIN_CHAIN, CASE WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '101' THEN 'ETHEREUM'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '102' THEN 'BSC'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '106' THEN 'AVALANCHE'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '109' THEN 'POLYGON'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '110' THEN 'ARBITRUM'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '111' THEN 'OPTIMISM'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '112' THEN 'FANTOM'

ELSE 'Other'

end as

destination_chain, to_date(a.block_timestamp) as date,

sum(t.raw_amount / pow(10, decimals)) as amount_usd

from ethereum.core.fact_event_logs a

join ethereum.core.fact_token_transfers t

on t.tx_hash = a.tx_hash

join ethereum.core.dim_contracts c

on t.contract_address = c.address

where a.contract_address in ('0xdf0770df86a8034b3efef0a1bb3c889b8332ff56','0x38ea452219524bb87e18de1c24d3bb59510bd783')

and a.topics[0] IN ('0x6939f93e3f21cf1362eb17155b740277de5687dae9a83a85909fd71da95944e7')

and a.origin_function_signature = '0x9fbf10fc'

group by ORIGIN_CHAIN, DESTINATION_CHAIN, date),

BSC as (

select count(distinct a.tx_hash) as tx_num, 'BSC' AS ORIGIN_CHAIN, CASE WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '101' THEN 'ETHEREUM'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '102' THEN 'BSC'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '106' THEN 'AVALANCHE'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '109' THEN 'POLYGON'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '110' THEN 'ARBITRUM'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '111' THEN 'OPTIMISM'

WHEN ethereum.public.udf_hex_to_int(substr(data, 3, 64)) = '112' THEN 'FANTOM'

ELSE 'Other'

end as destination_chain, to_date(a.block_timestamp) as date,

sum(t.raw_amount / 1e18) as amount_usd -- only has 1e18 tokens