flipside

    blast bridges
    damidezblast bridges
    Updated 2024-12-17
    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
    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
    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 date,
    Total_Transaction,
    SUM(Total_Transaction) OVER (ORDER BY date) AS TotalTransactions,
    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

    QueryRunArchived: QueryRun has been archived