SELECT tx_hash, src_chain, event_name, decoded_log, block_timestamp AS date

FROM (

SELECT * , 'ethereum' AS src_chain FROM ethereum.core.ez_decoded_event_logs

UNION

SELECT *, 'polygon' AS src_chain FROM polygon.core.ez_decoded_event_logs

UNION

SELECT *, 'bsc' AS src_chain FROM bsc.core.ez_decoded_event_logs

UNION

SELECT *, 'base' AS src_chain FROM base.core.ez_decoded_event_logs

UNION

SELECT *, 'arbitrum' AS src_chain FROM arbitrum.core.ez_decoded_event_logs

UNION

SELECT *, 'optimism' AS src_chain FROM optimism.core.ez_decoded_event_logs

UNION

SELECT *, 'avalanche' AS src_chain FROM avalanche.core.ez_decoded_event_logs

UNION

SELECT *, 'gnosis' AS src_chain FROM gnosis.core.ez_decoded_event_logs

)

WHERE origin_function_signature = '0x3ce33bff'

AND block_timestamp >= '{{from}}'

),

LiFi_Amarok_txns AS (

SELECT 'lifi' AS aggregator, a.tx_hash, a.src_chain, b.event_name, a.date

FROM MM_init a , MM_init b

WHERE a.event_name = 'XCalled' AND b.event_name = 'LiFiAmarokTransfer'

AND a.tx_hash = b.tx_hash

),

bridge_txns AS(

SELECT

CASE

WHEN event_name IN ('LiFiTransferStarted', 'LiFiAcrossTransfer', 'LiFiCBridgeTransfer', 'LiFiHopTransfer', 'LiFiAmarokTransfer') THEN 'lifi'

WHEN event_name = 'SocketBridge' THEN 'socket'

WHEN event_name = 'ContractCallWithToken' THEN 'squid'

ELSE 'other'

END AS aggregator,