WITH

active_users as (

SELECT

distinct ORIGIN_FROM_ADDRESS as users,

project_name,

min(trunc(block_timestamp,'week')) as weeks

from base.core.fact_event_logs x

join base.core.dim_labels y on x.contract_address=y.address

where label_type='dapp'

group by 1,2

),

ranks as (

select

trunc(weeks,'week') as date,

project_name as project,

count(distinct users) as new_users,

rank () over (partition by date order by new_users desc) as ranks

from active_users

group by 1,2

order by 1 asc

) select * from ranks where ranks<=10 order by 1 asc