SELECT

DATE_TRUNC('day', block_timestamp) AS date,

tx_hash,

decoded_log:user AS user,

Event_name,

SUM(decoded_log:amount / pow(10,18)) AS Total_Amount_eth

FROM

blast.core.ez_decoded_event_logs

WHERE

block_timestamp BETWEEN '2024-06-26' AND '2024-07-26'

AND EVENT_NAME IN ('BridgedDeposit', 'BridgedWithdrawal')

AND tx_status = 'SUCCESS'

AND Contract_address = '0x5e023c31e1d3dcd08a1b3e8c96f6ef8aa8fcacd1'

GROUP BY

date, tx_hash, decoded_log:user, Event_name

),

EthPrice AS (

SELECT

TRUNC(hour, 'day') AS day,

AVG(price) AS price_usd

FROM

blast.price.ez_prices_hourly

WHERE

symbol = 'ETH'

GROUP BY

day

SELECT

'Bridge In' AS BridgeType,

COUNT(*) AS BridgeCount,

SUM(DepWith.Total_Amount_eth * EthPrice.price_usd) AS Amount_USD

FROM

DepWith