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

'All time' as period,dapp,

"Total unique users" as users from data2 where date ='2023-10-16' and ranks1<=10