Calculate Relayer TX Fee Totals

flipside_queries.md

Default SDK Implementation

1. List all fee denoms:

with ibc_msgs as (
    select
        l.tx_id
    from cosmos.core.fact_msg_attributes l
    where
        l.attribute_value like '/ibc.%'
        and l.attribute_value not like '%transfer%'
        and l.attribute_key = 'action'
),
fee_grants as (
    select
        tx_id,
        attribute_value as granter
    from cosmos.core.fact_msg_attributes
    where
        attribute_key = 'granter'
),
ibc_txs as (
    select
        tx.tx_id,
        coalesce(fg.granter, tx.tx_from) as address,
        tx.tx_from as original_address,
        tx.block_timestamp,
        tx.fee,
        tx.fee_denom,
        tx.fee / pow(10, 6) as fee_amount,
        case when fg.granter is not null then true else false end as is_granter
    from cosmos.core.fact_transactions tx
    left join fee_grants fg on tx.tx_id = fg.tx_id
    where
        tx.tx_id in (select tx_id from ibc_msgs)
)
select distinct fee_denom
from ibc_txs;

Take denoms as manual input for querying fee totals:

with ibc_msgs as (
    select
        l.tx_id
    from cosmos.core.fact_msg_attributes l
    where
        l.attribute_value like '/ibc.%'
        and l.attribute_value not like '%transfer%'
        and l.attribute_key = 'action'
),
fee_grants as (
    select
        tx_id,
        attribute_value as granter
    from cosmos.core.fact_msg_attributes
    where
        attribute_key = 'granter'
),
ibc_txs as (
    select
        tx.tx_id,
        tx.tx_from,
        coalesce(fg.granter, tx.tx_from) as address,
        tx.block_timestamp,
        tx.fee,
        tx.fee_denom,
        tx.fee / pow(10, 6) as fee_amount, -- << USE CHAIN SPECIFIC DECIMALS HERE
        case when fg.granter is not null then true else false end as is_granter
    from cosmos.core.fact_transactions tx
    left join fee_grants fg on tx.tx_id = fg.tx_id
    where
        tx.tx_id in (select tx_id from ibc_msgs)
)
select
    address,
    sum(case when fee_denom = 'uatom' then fee_amount else 0 end) as atom_spent, -- << USE FEE DENOM(S) TO MATCH HERE
    count(distinct tx_id) as total_txs,
    is_granter
from (
    select distinct tx_id, address, fee_amount, is_granter, fee_denom
    from ibc_txs
) as distinct_ibc_txs
group by address, is_granter
order by total_txs desc;

Osmosis Implementation

For Osmosis we need to query all fee_denoms used like this:

with ibc_msgs as (
    select
        l.tx_id
    from osmosis.core.fact_msg_attributes l
    where
        l.attribute_value like '/ibc.%'
        and l.attribute_value not like '%transfer%'
        and l.attribute_key = 'action'
),
ibc_txs as (
    select
        tx.tx_id,
        regexp_substr(tx.fee, '[0-9]+') as fee_value,
        regexp_substr(tx.fee, '[a-zA-Z]+') as fee_denom
    from osmosis.core.fact_transactions tx
    where
        tx.tx_id in (select tx_id from ibc_msgs)
)
select distinct fee_denom
from ibc_txs;

returns:

uosmo

Take denoms as manual input for querying fee totals on Osmosis:

with ibc_msgs as (
    select
        l.tx_id
    from osmosis.core.fact_msg_attributes l
    where
        l.attribute_value like '/ibc.%'
        and l.attribute_value not like '%transfer%'
        and l.attribute_key = 'action'
),
fee_grants as (
    select
        tx_id,
        attribute_value as granter
    from osmosis.core.fact_msg_attributes
    where
        attribute_key = 'granter'
),
parsed_fees as (
    select
        tx.tx_id,
        tx.tx_from,
        coalesce(fg.granter, tx.tx_from) as address,
        tx.block_timestamp,
        regexp_substr(tx.fee, '[0-9]+')::numeric / pow(10, 6) as fee_amount,
        regexp_substr(tx.fee, '[a-zA-Z]+$') as fee_denom,
        case when fg.granter is not null then true else false end as is_granter
    from osmosis.core.fact_transactions tx
    left join fee_grants fg on tx.tx_id = fg.tx_id
    where
        tx.tx_id in (select tx_id from ibc_msgs)
        and tx.fee is not null
        and regexp_substr(tx.fee, '[0-9]+') is not null
        and regexp_substr(tx.fee, '[a-zA-Z]+$') is not null
)
select
    address,
    sum(case when fee_denom = 'uosmo' then fee_amount else 0 end) as osmo_spent,
    count(distinct tx_id) as total_txs,
    is_granter
from (
    select distinct tx_id, address, fee_amount, is_granter, fee_denom
    from parsed_fees
) as distinct_ibc_txs
group by address, is_granter
order by total_txs desc;

osmosis_fee_totals.csv