emirprince95MINE & LUNA Staking in Time
Updated 2021-11-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
›
⌄
with normalization as (select sum(mine_staked) as sum_mine, sum(luna_staked) as sum_luna from(
select m.time_stamp as block_timestamp, mine_staked, luna_staked
from
(select date_trunc('day', block_timestamp) as time_stamp, sum(event_attributes:"1_amount"/1000000) as mine_staked from terra.msg_events
where event_type = 'wasm' AND event_attributes:"1_action" = 'staking' AND event_attributes:to = 'terra1xu8utj38xuw6mjwck4n97enmavlv852zkcvhgp'
group by time_stamp) as m
join
(select date_trunc('day', block_timestamp) as time_stamp, sum(msg_value:amount:amount/1000000) as LUNA_STAKED from terra.msgs
where msg_module = 'staking' AND msg_type = 'staking/MsgDelegate' AND tx_status = 'SUCCEEDED' AND msg_value:amount:denom = 'uluna'
group by time_stamp) as l
on l.time_stamp = m.time_stamp))
select *, norm_luna/norm_mine as luna_to_mine_staking_ratio from (
select m.time_stamp as block_timestamp, mine_staked, luna_staked, mine_staked/(select sum_mine from normalization) as norm_mine, luna_staked/(select sum_luna from normalization) as norm_luna
from
(select date_trunc('day', block_timestamp) as time_stamp, sum(event_attributes:"1_amount"/1000000) as mine_staked from terra.msg_events
where event_type = 'wasm' AND event_attributes:"1_action" = 'staking' AND event_attributes:to = 'terra1xu8utj38xuw6mjwck4n97enmavlv852zkcvhgp'
group by time_stamp) as m
join
(select date_trunc('day', block_timestamp) as time_stamp, sum(msg_value:amount:amount/1000000) as LUNA_STAKED from terra.msgs
where msg_module = 'staking' AND msg_type = 'staking/MsgDelegate' AND tx_status = 'SUCCEEDED' AND msg_value:amount:denom = 'uluna'
group by time_stamp) as l
on l.time_stamp = m.time_stamp)
Run a query to Download Data