messaritest - total daily staking rewards (luna denominated)
Updated 2022-05-02
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
›
⌄
-- Tarik - revised from query by crypgoat https://app.flipsidecrypto.com/dashboard/terra-validator-rewards-analysis-2Z6LF5
SELECT * FROM terra.msg_events
limit 5
with tester as (
SELECT
BLOCK_ID, TRANSITION_TYPE, CHAIN_ID, EVENT, EVENT_ATTRIBUTES, event_attributes:amount as test_unpack
FROM terra.transitions
WHERE CHAIN_ID = 'columbus-5'
AND block_timestamp > CURRENT_DATE - 100
AND event = 'rewards'
AND transition_type = 'end_block'
LIMIT 3)
select * from tester, lateral flatten(input => tester.test_unpack)
SELECT
*
FROM terra.transitions
WHERE CHAIN_ID = 'columbus-5'
AND block_timestamp > CURRENT_DATE - 100
AND event = 'rewards'
AND transition_type = 'end_block'
LIMIT 3
---------------------------
WITH rewards AS (
SELECT
date_trunc('day', block_timestamp) as date,
sum(event_attributes:amount[0]:amount::float) / pow(10,6) as luna_amt
FROM terra.transitions
WHERE block_timestamp > CURRENT_DATE - 500
AND block_timestamp < CURRENT_DATE
AND event = 'rewards'
AND transition_type = 'end_block'
GROUP BY 1
ORDER BY 1),
Run a query to Download Data