WITH duplicate_tx_hashes AS (

SELECT

tx_hash

FROM

NEAR.DEFI.EZ_BRIDGE_ACTIVITY

GROUP BY

tx_hash

HAVING

COUNT(*) > 1

),

filtered_transactions AS (

SELECT

TO_CHAR(DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', block_timestamp)), 'YYYY-MM-DD') AS date_pt,

source_chain,

amount_usd,

tx_hash,

CASE

WHEN direction = 'inbound'

AND destination_chain = 'near'

AND platform = 'rainbow'

AND token_address IN (

'111111111117dc0aa78b770fa6a738034120c302.factory.bridge.near',

'7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9.factory.bridge.near',

'c00e94cb662c3520282e6f5717214004a7f26888.factory.bridge.near',

'6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near',

'1f9840a85d5af5bf1d1762f925bdaddc4201f984.factory.bridge.near',

'17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1',

'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near',

'dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near',

'usdt.tether-token.near',

'2260fac5e5542a773aa44fbcfedf7c193bc2c599.factory.bridge.near',

'c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2.factory.bridge.near',

'aurora'

) THEN amount_usd