SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,

COUNT(TX_HASH) AS Total_Transaction,

COUNT(DISTINCT decoded_log:user) AS Number_of_Unique_User,

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

FROM

blast.core.ez_decoded_event_logs

WHERE

block_timestamp::date >= '2024-06-02'

AND block_timestamp::date <= '2024-07-02'

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

AND tx_status = 'SUCCESS'

AND Contract_address = '0x5e023c31e1d3dcd08a1b3e8c96f6ef8aa8fcacd1'

GROUP BY

date

ORDER BY

date DESC

),

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

s.date,

s.Total_Transaction,

SUM(s.Total_Transaction) OVER (ORDER BY s.date) AS TotalTransactions,