Hosein-maleki-9555Osmosis 25 - Daily number of Incoming transfers To Osmosis via Axelar Satellite Bridge
    Updated 2022-11-16
    with pricet as (
    select recorded_at::date as day,
    lower (symbol) as symbol,
    avg (price) as USDPrice
    from osmosis.core.dim_prices
    group by 1,2

    union ALL

    select recorded_at::date as day,
    'axl' as symbol2,
    avg (price) as USDPrice
    from osmosis.core.dim_prices
    where symbol ilike 'axl'
    group by 1,2),

    Table1 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 Symbol
    from axelar.core.fact_transfers
    where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT')
    and TX_SUCCEEDED = 'TRUE'),

    Table2 as (
    select t1.*,
    (amount*USDPrice / POW (10,decimal)) as USDVolume
    from Table1 t1 left join pricet t2 on t1.block_timestamp::date = t2.day and t1.symbol = t2.symbol

    union ALL

    select *,
    regexp_substr (sender,'[a-zA-Z]+|\d+') as Sender_Chain,
    Run a query to Download Data