nsa2000osmo met11
    Updated 2023-02-11
    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 date >='2022-12-01'
    and date < '2023-01-01'
    group by 1, 2
    having dt_cnt >= 4
    )
    , top50 as (
    SELECT
    receiver,
    count(distinct tx_id) as tx_cnt
    FROM osmosis.core.fact_transfers
    where
    transfer_type = 'IBC_TRANSFER_IN'
    and receiver in (select active_users from active_user)
    GROUP by 1
    order by 2 DESC
    limit 50
    )
    select
    concat(LEFT(receiver, 8),'...',RIGHT(receiver, 4)) as users,
    CASE
    when CURRENCY iLIKE ('%osmo%') then 'OSMO'
    when CURRENCY iLIKE ('%juno%') then 'OSMO'
    when CURRENCY iLIKE ('%atom%') then 'Atom'
    when CURRENCY iLIKE ('%gravity%') then 'gravity'
    when CURRENCY iLIKE ('%ki%') then 'ki'
    ELSE CURRENCY end as Asset,
    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
    Run a query to Download Data