HaisenbergOlas-Source chains copy
Updated 2024-12-26Copy Reference Fork
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
›
⌄
-- 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