0xHaM-dAverage wMATIC
Updated 2023-02-12Copy 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
›
⌄
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