0xHaM-dAverage wMATIC
    Updated 2023-02-12
    with tb1 as (
    SELECT
    date_trunc('day', block_timestamp) as date,
    CASE
    WHEN date > '2022-12-19' THEN 'Bootstrapping Incentives'
    ELSE 'Regular Incetivsed' END as duration,
    COUNT(DISTINCT RECEIVER) as bridger_cnt,
    COUNT(DISTINCT TX_ID) as bridges_cnt,
    sum(amount / power(10, decimal)) as bridge_vol,
    avg(amount / power(10, decimal)) as avg_bridge_vol,
    avg(bridger_cnt) over (order by date rows between 6 PRECEDING and current row) as "MA 7-day bridger Cnt",
    avg(bridges_cnt) over (order by date rows between 6 PRECEDING and current row) as "MA 7-day bridges Cnt",
    avg(bridge_vol) over (order by date rows between 6 PRECEDING and current row) as "MA 7-day bridge vol",
    sum(bridges_cnt) over (partition by duration ORDER by date) as cume_bridges_cnt,
    sum(bridge_vol) over (partition by duration ORDER by date) as cume_bridge_vol
    FROM osmosis.core.fact_transfers
    LEFT outer JOIN osmosis.core.dim_labels
    ON address = currency
    WHERE project_name IN ('wMATIC') -- ('wAVAX', 'wBNB', 'wMATIC')
    AND sender LIKE 'axelar%'
    AND receiver LIKE 'osmo%'
    GROUP BY 1,2
    order by 1
    )
    SELECT
    duration,
    round(avg(bridger_cnt), 2) as avg_bridger_cnt,
    round(avg(bridges_cnt), 2) as avg_bridges_cnt,
    round(avg(avg_bridge_vol), 2) as avg_avg_bridge_vol
    FROM tb1
    GROUP by 1
    ORDER by 2

    Run a query to Download Data