feyikemiAvalanche Bridge Totals 1
    Updated 2024-06-18
    WITH bridge_activity AS (
    SELECT
    date_trunc('day', BLOCK_TIMESTAMP) AS day,
    COUNT(DISTINCT TX_HASH) AS total_bridges,
    COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS total_bridgers,
    SUM(CASE WHEN AMOUNT_USD < 1e9 THEN AMOUNT_USD ELSE 0 END) AS total_amount_bridged
    FROM
    avalanche.defi.ez_bridge_activity
    WHERE
    BLOCK_TIMESTAMP::DATE >= '2024-01-01'
    GROUP BY 1
    ),

    total_metrics AS (
    SELECT
    SUM(total_bridges) AS total_bridges,
    SUM(total_bridgers) AS total_bridgers,
    SUM(total_amount_bridged) AS total_volume_bridged
    FROM
    bridge_activity
    ),

    weekly_bridge_metrics AS (
    SELECT
    SUM(total_bridges) AS weekly_bridges,
    SUM(total_bridgers) AS weekly_bridgers,
    SUM(total_amount_bridged) AS weekly_volume_bridged
    FROM
    bridge_activity
    WHERE
    day >= current_date - INTERVAL '7 days'
    )

    SELECT
    tm.total_bridges AS "Total Avalanche Bridges",
    wm.weekly_bridges AS "Total Avalanche Bridges Past 7 Days",
    QueryRunArchived: QueryRun has been archived