WITH

same_chain_txns as (

SELECT a.*, 'Arbitrum' as chain FROM arbitrum.core.ez_decoded_event_logs a, arbitrum.core.ez_decoded_event_logs b

WHERE a.block_timestamp between current_date -31 AND current_date-1

AND b.block_timestamp between current_date -31 AND current_date-1

AND a.tx_hash = b.tx_hash

AND a.event_name = 'LiFiGenericSwapCompleted'

AND b.event_name = 'FlashLoan' -- in ('ReserveUsedAsCollateralEnabled', 'ReserveUsedAsCollateralDisabled')

UNION ALL

SELECT a.*, 'OP' as chain FROM optimism.core.ez_decoded_event_logs a,optimism.core.ez_decoded_event_logs b

WHERE a.block_timestamp between current_date -31 AND current_date-1

AND b.block_timestamp between current_date -31 AND current_date-1

AND a.tx_hash = b.tx_hash

and a.event_name = 'LiFiGenericSwapCompleted'

AND b.event_name = 'FlashLoan'

UNION ALL

SELECT a.*, 'BSC' as chain FROM bsc.core.ez_decoded_event_logs a, bsc.core.ez_decoded_event_logs b

WHERE a.block_timestamp between current_date -31 AND current_date-1

AND b.block_timestamp between current_date -31 AND current_date-1

AND a.tx_hash = b.tx_hash

and a.event_name = 'LiFiGenericSwapCompleted'

AND b.event_name = 'FlashLoan'

UNION ALL

SELECT a.*, 'BASE' as chain FROM base.core.ez_decoded_event_logs a, base.core.ez_decoded_event_logs b

WHERE a.block_timestamp between current_date -31 AND current_date-1

AND b.block_timestamp between current_date -31 AND current_date-1

AND a.tx_hash = b.tx_hash

and a.event_name = 'LiFiGenericSwapCompleted'

AND b.event_name = 'FlashLoan'

UNION ALL