WITH Summbriges AS (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 date >= '2024-06-26' AND 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 (

trunc(hour,'day') as day,

AVG(price) as price_usd

from blast.price.ez_prices_hourly

where symbol='ETH'

group by day

SELECT date,

Total_Transaction,

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

Total_Amount_eth,

Total_Amount_eth * e.price_usd AS Amount_USD,

SUM(Amount_USD) OVER (ORDER BY date) AS Total_bridged_USD

FROM Summbriges s

JOIN Ethprice e

ON s.date = e.day

ORDER BY date DESC