NILOOZHANNUALIZED_STAKING
Updated 2022-01-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 staked as (
select
date_trunc('day',date) as day, sum(balance) as stakedluna
from terra.daily_balances
where currency = 'LUNA'
and balance_type = 'staked'
and day >= '2021-01-01'
and day <= '2022-12-31'
group by 1),
lunablockfees as (
select
date_trunc('day', block_timestamp) as day,
sum(event_attributes:amount[0]:amount /pow(10,6)) as block_rewards_luna
from terra.transitions
where transition_type = 'end_block'
and event_attributes:amount[0]:denom = 'uluna'
and(event = 'rewards' or event = 'commissions')
and day >= '2021-03-12'
and day <= '2021-05-12'
group by 1),
prices as (
select
date_trunc('day', block_timestamp) as day,
avg(price_usd) as luna_price
from terra.oracle_prices
where symbol = 'LUNA'
group by 1
)
SELECT
s.day,
s.stakedluna*p.luna_price as staked_value,
b.block_rewards_luna*p.luna_price as block_value,
(block_value/staked_value)*365*100 as annualized_staking_apy