WITH

daus as (

SELECT

distinct tx_signer as users,

trunc(block_timestamp,'week') as weeks,

count(distinct trunc(block_timestamp,'day')) as active_days

from near.core.fact_transactions

group by 1,2

having active_days>=4

),

active_users as (

SELECT

distinct tx_signer,

tx_receiver,

min(x.block_timestamp) as debut

from near.core.fact_transactions x

where tx_signer in (select users from daus)

group by 1,2

),

final as (

select

trunc(debut,'week') as date,

project_name as name,

count(distinct tx_signer) as n_users,

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

from active_users x

join near.core.dim_address_labels y on x.tx_receiver=y.address

where date>=current_date-interval '{{Months}} MONTHS'

and label_type='token'

group by 1,2

order by 1 asc

),

final2 as (

date,