nsa2000top 10 assets most transferred by top 50 senders (according to the number of transactions or volume of token transferred).
    Updated 2022-10-19
    with active_user as (
    select
    date_trunc('week', block_timestamp)::date as date,
    tx_from as active_users,
    count(distinct block_timestamp::date) as dt_cnt
    from osmosis.core.fact_transactions
    where tx_status = 'SUCCEEDED'
    group by 1, 2
    having dt_cnt >= 4
    )
    , top50 as (
    SELECT
    sender,
    count(distinct tx_id) as tx_cnt
    FROM osmosis.core.fact_transfers
    where tx_status = 'SUCCEEDED'
    and transfer_type = 'IBC_TRANSFER_OUT'
    and sender in (select active_users from active_user)
    GROUP by 1
    order by 2 DESC
    limit 50
    )
    select
    concat(LEFT(sender, 8),'...',RIGHT(sender, 4)) as users,
    PROJECT_NAME,
    count(distinct tx_id) as tx_cnt,
    sum(AMOUNT/pow(10, DECIMAL)) as volume,
    RANK() OVER (PARTITION by users order by tx_cnt DESC) as rank
    from osmosis.core.fact_transfers a LEFT join osmosis.core.dim_labels b on a.currency = b.address
    where tx_status = 'SUCCEEDED'
    and transfer_type = 'IBC_TRANSFER_OUT'
    and sender in (select sender from top50)
    group by 1,2
    qualify rank <= 10