quenineblast bridging 2 copy
    Updated 2024-05-10
    -- forked from pentalimited / blast bridging 2 @ https://flipsidecrypto.xyz/pentalimited/q/6RT5rpaA9utS/blast-bridging-2

    WITH weekly_bridge_stats AS (
    SELECT
    date_trunc('week', x.BLOCK_TIMESTAMP) AS week,
    event_name,
    COUNT(DISTINCT x.TX_HASH) AS num_bridge_transactions,
    COUNT(DISTINCT decoded_log:user) AS num_bridge_users,
    SUM(decoded_log:amount/pow(10,18)) AS total_amount_bridged,
    sum(case when event_name='BridgedDeposit' then decoded_log:amount/pow(10,18) else (decoded_log:amount/pow(10,18))*(-1) end) as net_amount_bridged
    FROM
    blast.core.ez_decoded_event_logs x
    WHERE
    --x.BLOCK_TIMESTAMP >= '2024-02-29 10:00'
    x.BLOCK_TIMESTAMP < current_date
    and event_name in('BridgedDeposit','BridgedWithdrawal')
    GROUP BY 1,2
    ),
    prices as (
    SELECT
    trunc(hour,'week') as weeks,
    avg(price) as price_usd
    from blast.price.ez_prices_hourly
    where symbol='ETH'
    group by 1
    )

    SELECT
    week,
    event_name,
    num_bridge_transactions,
    sum(num_bridge_transactions) over (partition by event_name order by week) as total_bridges,
    num_bridge_users,
    case when event_name='BridgedDeposit' then total_amount_bridged*price_usd else total_amount_bridged*price_usd*(-1) end as total_amount_bridged_usd,
    sum(total_amount_bridged_usd) over (partition by event_name order by week) as total_volume_usd
    FROM
    QueryRunArchived: QueryRun has been archived