HaisenbergOlas-Source chains copy
    Updated 2024-12-26
    -- forked from MLDZMN / Source chains @ https://flipsidecrypto.xyz/MLDZMN/q/mp2LCwyAPdJM/source-chains

    with quarterly_bonds as (
    select
    p.SOURCE_CHAIN,
    date_trunc('quarter', p.BLOCK_TIMESTAMP) as quarter_date,
    'Q' || extract(quarter from p.BLOCK_TIMESTAMP) || ' ' ||
    extract(year from p.BLOCK_TIMESTAMP) as quarter_label,
    count(distinct p.tx_hash) as bonds,
    sum(OLAS_AMOUNT) as vol_olas,
    avg(OLAS_AMOUNT) as avg_vol_olas,
    count(distinct p.ORIGIN_FROM_ADDRESS) as bonders,
    vol_olas/bonders as avg_bond_per_bonders
    from crosschain.olas.fact_pol_transfers p
    left join crosschain.olas.ez_olas_bonding b on b.tx_hash = p.tx_hash
    --where p.block_timestamp::date >= '2021-01-01'
    group by 1, 2, 3
    )
    select
    SOURCE_CHAIN,
    quarter_label,
    bonds,
    vol_olas,
    avg_vol_olas,
    bonders,
    avg_bond_per_bonders,
    -- Adding quarter-over-quarter growth metrics
    100 * (vol_olas - lag(vol_olas) over (partition by SOURCE_CHAIN order by quarter_date)) /
    nullif(lag(vol_olas) over (partition by SOURCE_CHAIN order by quarter_date), 0) as qoq_vol_growth_pct,
    100 * (bonders - lag(bonders) over (partition by SOURCE_CHAIN order by quarter_date)) /
    nullif(lag(bonders) over (partition by SOURCE_CHAIN order by quarter_date), 0) as qoq_bonders_growth_pct
    from quarterly_bonds
    order by SOURCE_CHAIN, quarter_date


    QueryRunArchived: QueryRun has been archived