connorhWormhole: Outbound From Terra
Updated 2021-11-22Copy 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
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- All Terra tokens sent to each chain via https://wormholebridge.com/#/
WITH chains AS (
SELECT
column1 AS chain_id,
column2 AS chain
FROM (
VALUES (1,'Solana'),
(2,'Ethereum'),
(3,'Terra'),
(4,'Binance'),
(5,'Polygon')
)
), wormhole_txs_outbound AS (
SELECT
DATE(block_timestamp) AS balance_date,
msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom::STRING AS denom,
msg_value:execute_msg:initiate_transfer:recipient_chain AS recipient_chain,
SUM(msg_value:execute_msg:initiate_transfer:asset:amount::NUMERIC/POWER(10,6)) AS amount_token
FROM terra.msgs
WHERE recipient_chain IS NOT NULL AND block_timestamp >= CURRENT_DATE - 90 AND denom IS NOT NULL
GROUP BY 1,2,3
),terra_prices_daily AS (
-- Going with daily as there are some serious gaps in the price data
SELECT date_trunc('day',block_timestamp) AS day,currency, AVG(price_usd) AS price
FROM terra.oracle_prices
WHERE block_timestamp >= CURRENT_DATE - 90
GROUP BY 1,2
)
SELECT
c.chain AS recipient_chain,
SUM(w.amount_token*o.price) AS total_sent
FROM wormhole_txs_outbound w
LEFT JOIN
Run a query to Download Data