PS0G1Ratio of Matic staked vs its circulation
    Updated 2022-09-23
    --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