jp122 [Optimism] Bridge Volume Comparison - ETH Deposit vs Withdraw
    Updated 2022-07-21
    -- bridges as (
    -- select ADDRESS
    -- from ethereum.core.DIM_LABELS
    -- where LABEL = 'hop protocol' and address_name ilike '%bridge%'
    -- )

    WITH tx as (
    select block_timestamp::date as date, 'Hop Protocol' as bridge, IFF(eth_to_address = '0xb8901acb165ed027e32754e0ffe830802919727f', 'Deposit', 'Withdraw') as type,
    IFF(eth_to_address = '0xb8901acb165ed027e32754e0ffe830802919727f', amount, -amount) as actual_amount,*
    from ethereum.core.ez_eth_transfers
    where eth_from_address = '0xb8901acb165ed027e32754e0ffe830802919727f' OR eth_to_address = '0xb8901acb165ed027e32754e0ffe830802919727f'

    UNION

    select block_timestamp::date as date, 'Native' as bridge, IFF(eth_to_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1', 'Deposit', 'Withdraw') as type,
    IFF(eth_to_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1', amount, -amount) as actual_amount, *
    from ethereum.core.ez_eth_transfers
    where eth_to_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1' OR eth_from_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'
    )

    SELECT date, bridge, type, SUM(actual_amount) as eth_amount
    FROM tx
    GROUP BY 1, 2, 3
    Run a query to Download Data