zanglangMy IBC Packets by Day
    Updated 2024-03-10
    with my_txs as (
    select
    tx_id
    from cosmos.core.fact_transactions
    where tx_succeeded = 1
    and tx_from IN ('cosmos1p7d8mnjttcszv34pk2a5yyug3474mhffasa7tg', 'cosmos1nna7k5lywn99cd63elcfqm6p8c5c4qcug4aef5')
    and block_timestamp >= CURRENT_DATE - interval '{{ day }} day'
    ),
    ibc_msgs as (
    select
    tx_id,
    block_timestamp,
    split_part(attribute_value, '.', 5) as pkt_type
    from cosmos.core.fact_msg_attributes
    where tx_succeeded = 1
    and attribute_key = 'action'
    and attribute_value like '/ibc.%'
    and tx_id in (select tx_id from my_txs)
    and block_timestamp >= CURRENT_DATE - interval '{{ day }} day'
    ),
    dst_msgs as (
    select
    tx_id,
    attribute_value
    from cosmos.core.fact_msg_attributes
    where attribute_key = 'packet_src_channel'
    and tx_id in (select tx_id from ibc_msgs)
    and block_timestamp >= CURRENT_DATE - interval '{{ day }} day'
    ),
    final as (
    select
    l.tx_id,
    l.block_timestamp,
    r.attribute_value as src_channel
    from ibc_msgs l join dst_msgs r on l.tx_id = r.tx_id
    )
    Auto-refreshes every 12 hours
    QueryRunArchived: QueryRun has been archived