zanglangIBC Relayers Shares (Sei)
Updated 2023-09-11
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
36
›
⌄
-- forked from IBC Relayers Shares @ https://flipsidecrypto.xyz/edit/queries/ba355a85-0f86-4e3e-9f81-6ba41ceee5b0
with ibc_msgs as (
select
tx_id,
block_timestamp,
split_part(attribute_value, '.', 5) as pkt_type
from sei.core.fact_msg_attributes
where tx_succeeded = 1
and attribute_key = 'action'
and attribute_value like '/ibc.%'
and block_timestamp >= CURRENT_DATE - interval '{{ day }} day'
),
ibc_txs as (
select
tx_id,
block_timestamp,
tx_from
from sei.core.fact_transactions
where tx_succeeded = 1
and tx_id in (select tx_id from ibc_msgs)
and block_timestamp >= CURRENT_DATE - interval '{{ day }} day'
),
final as (
select
date_trunc('hh', block_timestamp)::date as date,
tx_from,
count(distinct tx_id) as txs
from ibc_txs
group by 1,2
)
select
date,
tx_from,
txs
from final
Run a query to Download Data