PS0G1Popular bridger destinations by volume and transactions
    Updated 2022-10-28
    -- credit alik110
    with prices as (
    select
    date(recorded_at) as pdate, lower(symbol) as symbol,
    avg(price) as price_usd
    from osmosis.core.dim_prices
    group by pdate, symbol
    ),
    raw as (
    select
    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,
    lower(split(currency,'-')[0]) as Symbol1,
    iff(Symbol1 ilike 'u%',substring(Symbol1,2,LEN(Symbol1)), Symbol1) as asset, *
    from axelar.core.fact_transfers t
    )
    select
    count(distinct TX_ID) as tx_count,
    sum(amount/pow(10,decimal) * price_usd) as tvol, initcap(Receiver_Chain)
    from raw t join prices on date(t.block_timestamp) = pdate and prices.symbol = asset
    where TX_SUCCEEDED = 'TRUE'
    and TRANSFER_TYPE = 'IBC_TRANSFER_OUT'
    and amount is not null
    group by Receiver_Chain
    Run a query to Download Data