mz0111bridges 8
Updated 2022-11-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with tab1 as
(select
sender,
regexp_substr (sender,'[a-zA-Z]+|\d+') as Sender_Chain,
regexp_substr (receiver,'[a-zA-Z]+|\d+') as Receiver_Chain,
Sender_Chain|| ' To ' || Receiver_Chain as Transfer_Path,
Min(date_trunc('day', block_timestamp)) as min_date,
Max(date_trunc('day', block_timestamp)) as max_date,
count(tx_id) as bridges
from axelar.core.fact_transfers
where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT')
AND not CURRENCY ilike any ('factory%', 'gravity%')
AND amount is not NULL
AND currency is not NULL
group by 1 , 2 , 3
having Min(date_trunc('day', block_timestamp)) > current_date - 90
)
select
Transfer_Path,
sum(datediff('day', min_date, max_date))/ sum(bridges) as Time_Between_bridges
from tab1
group by 1
Run a query to Download Data