emirprince95Average & Median LUNA-to-MINE Staking Ratio
    Updated 2021-11-11
    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))


    , full_table as (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))

    select avg(LUNA_TO_MINE_STAKING_RATIO) as avg_ratio, median(LUNA_TO_MINE_STAKING_RATIO) as median_ratio from full_table