shreyash-5873Terra Total Rewards Per Unit Luna Staked
Updated 2021-07-04
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
›
⌄
⌄
-- this one surely works
with total_rewards as (select
date(block_timestamp) as block_date,
sum(event_attributes:amount[0]:amount / POW(10, 6)) as Total_LUNA_REWARD
from terra.transitions
where transition_type = 'end_block'
and event = 'rewards'
and date(block_timestamp) >= CURRENT_DATE() - 31 and date(block_timestamp) < CURRENT_DATE() - 1
group by block_date
order by block_date desc
),
daily_validator_specific_voting_power as (
select
date(block_timestamp) as block_date,
address,
avg(voting_power) as daily_average_voting_power
from terra.validator_voting_power
group by block_date, address),
daily_voting_power as (
select
block_date,
sum(daily_average_voting_power) as daily_vp
from daily_validator_specific_voting_power
group by block_date
)
select
r.block_date,
r.total_luna_reward,
v.daily_vp,
r.total_luna_reward / v.daily_vp * 100 as DAILY_APR
from total_rewards r
inner join daily_voting_power v
on r.block_date = v.block_date
order by daily_apr desc;
/* select
date(block_timestamp) as block_date,
Run a query to Download Data