eferPlayground - Optimism daily bridges (Last 60days)
Updated 2022-06-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
-- deposits
-- withdrawals
WITH native_bridge AS (
SELECT
CAST(DATE_TRUNC('day', BLOCK_TIMESTAMP) AS DATE) AS date,
COUNT(DISTINCT ETH_FROM_ADDRESS) AS senders,
SUM(AMOUNT) AS eth,
SUM(eth) over (ORDER BY date ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS cumulative,
SUM(senders) over (ORDER BY date ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS cumulative_depositors
FROM ethereum.core.ez_eth_transfers
WHERE ETH_TO_ADDRESS = '0x52ec2f3d7c5977a8e558c8d9c6000b615098e8fc' -- Optimism Wrap Speed Bridge Contract (V2)
-- OR ETH_TO_ADDRESS = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1' -- Optimism Main Bridge Contract (V1)
AND date > CURRENT_DATE - 60
GROUP BY date
), old_bridge AS (
SELECT
CAST(DATE_TRUNC('day', BLOCK_TIMESTAMP) AS DATE) AS date,
COUNT(DISTINCT ETH_FROM_ADDRESS) AS senders,
SUM(AMOUNT) AS eth
FROM ethereum.core.ez_eth_transfers
WHERE ETH_TO_ADDRESS = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1' -- Optimism Main Bridge Contract (V1)
AND date > CURRENT_DATE - 60
GROUP BY date
)
SELECT
old_bridge.date, COALESCE(native_bridge.eth, 0) AS native_bridge_eth, native_bridge_eth + old_bridge.eth AS both_bridges_eth
FROM native_bridge, old_bridge
RIGHT join native_bridge on old_bridge.date = native_bridge.date
ORDER BY DATE DESC
/*
IF NOT EXISTS (SELECT old_bridge.date FROM native_bridge, old_bridge)
SELECT
old_bridge.date, 0 AS native_bridge_eth, native_bridge_eth + old_bridge.eth AS both_bridges_eth
Run a query to Download Data