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 >='2023-01-01'

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 transfer_type = 'IBC_TRANSFER_OUT'

and sender in (select active_users from active_user)

GROUP by 1

order by 2 DESC

limit 50

)

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 transfer_type = 'IBC_TRANSFER_OUT'

and sender in (select sender from top50)

group by 1,2

qualify rank <= 10