-- forked from New Users vs. Onboarding Users to Projects @ https://flipsidecrypto.xyz/edit/queries/9ca6a0d0-052c-44b3-a76c-b4d21a102a55

with projectsTb as (

select

address,

project_name as project,

label_type

from

near.core.dim_address_labels

where

project_name is not null

and label_type not in ('chadmin', 'cex', 'token', 'fungible_token')

and project_name != 'Exchanges'

),

new_usersTb as (

select

tx_signer,

min(block_timestamp) as onboarding_timestamp

from

near.core.fact_transactions

where

tx_signer not in (

select

address

from

projectsTb

)

and TX_SUCCEEDED = 'True' --qualify rank = 1

GROUP BY

1

),

top10_procets_new_usr as (

select

project,

count(distinct t.tx_signer) as projects_new_usrs