maggieholahanCopy of Untitled Query
Updated 2021-08-18
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
›
⌄
WITH to_bridge AS (
SELECT
date_trunc('day', block_timestamp) AS day,
sum(amount_usd) AS to_arbitrum,
COUNT(DISTINCT origin_address) AS addresses_to_arbitrum -- using origin address to sidestep inboxes
-- might want to do some more stuff on the inbound paths from a router perspective
FROM
ethereum.udm_events
WHERE
block_timestamp >= CURRENT_DATE -90
AND to_address = LOWER('0x011b6e24ffb0b5f5fcc564cf4183c5bbbc96d515') -- Arbitrum Bridge
GROUP BY
day
),
from_bridge AS (
SELECT
date_trunc('day', block_timestamp) AS day,
sum(amount_usd) * -1 AS from_arbitrum,
COUNT(DISTINCT origin_address) * -1 AS addresses_from_arbitrum
-- not really sure why the to_address/origin_address is 1 when a different query has a higher number?
FROM
ethereum.udm_events
WHERE
block_timestamp >= CURRENT_DATE -90 AND
from_address = LOWER('0x011b6e24ffb0b5f5fcc564cf4183c5bbbc96d515') -- Arbitrum Bridge
GROUP BY
day
)
SELECT
d.day,
d.to_arbitrum,
w.from_arbitrum,
d.addresses_to_arbitrum,
w.addresses_from_arbitrum
FROM
to_bridge d