sepehrmhz8Untitled Query
    Updated 2022-06-26
    with lst_top as (
    select top 10
    To_CURRENCY
    ,count (To_CURRENCY) as tx_count
    from osmosis.core.fact_swaps
    where BLOCK_TIMESTAMP >= CURRENT_DATE -175
    and From_CURRENCY = 'ibc/0CD3A0285E1341859B5E86B6AB7682F023D03E97607CCC1DC95706411D866DF7'
    and FROM_AMOUNT>0
    and TX_STATUS='SUCCEEDED'
    group by 1
    order by tx_count DESC
    )
    select
    block_timestamp::date as day
    ,(select min(PROJECT_NAME) from osmosis.core.dim_labels where ADDRESS=To_CURRENCY) as asset_name
    ,count (DISTINCT tx_id) as tx_count
    ,sum (TO_AMOUNT/pow(10,6)) as volume
    from osmosis.core.fact_swaps
    where BLOCK_TIMESTAMP >= CURRENT_DATE -175
    and From_CURRENCY = 'ibc/0CD3A0285E1341859B5E86B6AB7682F023D03E97607CCC1DC95706411D866DF7'
    and FROM_AMOUNT>0
    and TX_STATUS='SUCCEEDED'
    and To_CURRENCY in(select lst_top.To_CURRENCY from lst_top)
    group by 1,2
    order by 1