connorhAll Terra Coins and Luna Burns
Updated 2021-11-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
›
⌄
WITH burns AS (
-- BURNS
SELECT
r.tx_id,r.block_timestamp,
a.value[0]:denom::STRING AS denom,
SUM(a.value[0]:amount::NUMERIC) AS amount
FROM terra.msg_events r,
lateral flatten(input => r.event_attributes) a
WHERE block_timestamp >= CURRENT_DATE - 40 AND event_type = 'burn'
AND a.value[0]:denom::STRING IS NOT NULL
GROUP BY 1,2,3
), burners AS( --not actually sure if its ever different from terra1untf85jwv3kt0puyyc39myxjvplagr3wstgs5s (mystery address that never appears in the block explorer)
-- BURNERS
SELECT DISTINCT
r.tx_id,r.block_timestamp,
value::STRING AS burner
FROM terra.msg_events r,
lateral flatten(input => r.event_attributes) a -- this deals with multi burns/mints
WHERE block_timestamp >= CURRENT_DATE - 40 AND event_type = 'burn'
AND a.value[0]:denom::STRING IS NULL AND a.key REGEXP '.*burner'
), senders AS (
SELECT DISTINCT tx_id, COALESCE(msg_value:sender::STRING,msg_value:trader::STRING) AS sender
FROM terra.msgs m
WHERE block_timestamp >= CURRENT_DATE - 40
AND (msg_value:sender::STRING IS NOT NULL OR msg_value:trader::STRING IS NOT NULL)
), 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 - 40
GROUP BY 1,2
)
Run a query to Download Data