SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transfer_day,

SOURCE_CHAIN,

DESTINATION_CHAIN,

SUM(AMOUNT_USD) AS daily_liquidity,

COUNT(*) AS daily_transfers

FROM near.defi.ez_bridge_activity

WHERE BLOCK_TIMESTAMP >= DATEADD(day, -30, '2024-11-09')

AND RECEIPT_SUCCEEDED = TRUE

GROUP BY

transfer_day,

SOURCE_CHAIN,

DESTINATION_CHAIN

SELECT

transfer_day,

SOURCE_CHAIN,

DESTINATION_CHAIN,

daily_liquidity,

daily_transfers,

daily_liquidity / NULLIF(daily_transfers, 0) AS avg_transfer_size

FROM daily_liquidity

ORDER BY transfer_day, SOURCE_CHAIN, DESTINATION_CHAIN;