PS0G1Ratio of Matic staked vs its circulation
Updated 2022-09-23
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
›
⌄
--credit manizade
with act1 as(
with tab1 as(
select HOUR::date as date,avg(PRICE) as matic_price
from ethereum.core.fact_hourly_token_prices
where TOKEN_ADDRESS='0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0'
and date>='2022-07-01'
group by 1)
select date,matic_price as matic_usd,matic_usd*8730000000 as circul_usd
from tab1
group by 1,2),
---------------------------------------------------------------------------
act2 as (
with tab1 as(
select BLOCK_TIMESTAMP::date as date,sum(EVENT_INPUTS:amount)/1e18 as staked_matic
from ethereum.core.fact_event_logs
where ORIGIN_FUNCTION_SIGNATURE='0x6ab15071'
and CONTRACT_NAME='StakingInfo'
and EVENT_NAME='ShareMinted'
and TX_STATUS='SUCCESS'
and date>='2022-07-01'
group by 1),
tab2 as (
select HOUR::date as date,avg(PRICE) as matic_price
from ethereum.core.fact_hourly_token_prices
where TOKEN_ADDRESS='0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0'
and date>='2022-07-01'
group by 1)
select tab2.date,staked_matic*matic_price as staked_matic_usd
from tab1
left join tab2
on tab1.date=tab2.date
group by 1,2)
select sum(staked_matic_usd)/avg(circul_usd) as st_ratio_usd
from act1
left join act2
Run a query to Download Data