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

group by 1,2

),

data as (

select

trunc(weeks,'week') as date,

project_name as dapp,

count(distinct users) as new_users,

sum(new_users) over (partition by dapp order by date) as "Total unique users"

from active_users

group by 1,2

order by 1 asc

),

data2 as (

select

date,

dapp,

new_users,

"Total unique users",

rank () over (partition by date order by new_users desc) as ranks1,

rank () over (partition by date order by "Total unique users" desc) as ranks2

from data

select dapp,new_users from data2 where date ='2023-10-16' and ranks2<=10