jp122 [Optimism] Bridge Volume Comparison - ETH Deposit vs Withdraw
Updated 2022-07-21Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
-- 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