Maditotal
    Updated 2023-01-18
    with prices AS
    (
    SELECT date_trunc('day', RECORDED_AT) as date_price, symbol, avg(price) as price
    FROM osmosis.core.dim_prices
    GROUP BY 1,2
    ),

    df as (
    select date_trunc('day', BLOCK_TIMESTAMP) as date,
    TRANSFER_TYPE, tx_id, SENDER, RECEIVER,
    amount, amount/pow(10,DECIMAL) as amount_usd, UPPER(split(currency,'-')[0]) AS CURRENCY
    from axelar.core.fact_transfers
    where TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT') and TX_SUCCEEDED = 'TRUE' and date >= '2022-01-26' AND not CURRENCY ilike any ('factory%', 'gravity%') and amount is not null and currency is not null)

    select date, TRANSFER_TYPE, count(DISTINCT tx_id) as "Number of Bridges", sum(amount_usd) as "Total Amount, USD", Currency as Asset,
    sum("Number of Bridges") over (order by date asc rows between unbounded preceding and current row) as "Cumulative number of Bridges"
    from(
    select df.* from df left join prices on df.date = prices.date_price and df.currency = prices.symbol)
    group by 1,2, 5

    Run a query to Download Data