WITH swap_raw AS (

SELECT

block_timestamp,

blockchain as chain,

tx_hash,

CONCAT(symbol_out, '-', symbol_in) as pool_name,

contract_address as pool_contract_address,

trader,

token_in,

symbol_in,

amount_in_raw,

amount_in,

amount_in_usd,

token_out,

symbol_out,

amount_out_raw,

amount_out,

amount_out_usd,

'WETH' AS price_table_symbol

FROM crosschain.defi.ez_dex_swaps

WHERE block_timestamp::date >= '2023-01-01'

AND blockchain IN ('arbitrum', 'base', 'optimism')

)

SELECT

s.*,

COALESCE(t.tx_fee, 0) AS txn_fee,

COALESCE(t.tx_fee, 0) * p.price AS txn_fee_usd

FROM swap_raw s

LEFT JOIN (

SELECT tx_hash, tx_fee FROM arbitrum.core.fact_transactions WHERE block_timestamp::date >= '2023-01-01'

UNION ALL

SELECT tx_hash, tx_fee FROM base.core.fact_transactions WHERE block_timestamp::date >= '2023-01-01'

UNION ALL

SELECT tx_hash, tx_fee FROM optimism.core.fact_transactions WHERE block_timestamp::date >= '2023-01-01'

) t ON s.tx_hash = t.tx_hash