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

block_timestamp,

DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', block_timestamp)) AS date_pt,

amount_usd,

direction,

tx_hash,

CASE

WHEN direction = 'inbound' AND destination_chain = 'near' THEN amount_usd

ELSE 0

END AS inflow,

CASE

WHEN direction = 'outbound' AND source_chain = 'near' THEN amount_usd

ELSE 0

END AS outflow

FROM

NEAR.DEFI.EZ_BRIDGE_ACTIVITY

WHERE

receipt_succeeded = TRUE

AND NOT (direction = 'inbound' AND destination_chain = 'aurora')

AND tx_hash NOT IN (SELECT tx_hash FROM duplicate_tx_hashes)

),

aggregated_data AS (

SELECT