with base as (

select

block_timestamp,

tx_signer as user_address,

address as contract,

project_name

from near.core.fact_transactions

inner join (select address, project_name from near.core.dim_address_labels

where label_type not in ('token', 'cex')

and label_subtype not in ('token_contract', 'nf_token_contract')

and blockchain = 'near')

on address = tx_receiver

where tx_status = 'Success'

and tx_signer not in (select distinct address from near.core.dim_address_labels)

and year(block_timestamp) >= {{base_date}}

qualify(row_number() over (partition by user_address, project_name order by block_timestamp)) = 1

),

contracts as (

select BLOCK_TIMESTAMP, creator, contract, tx_hash

from

(select BLOCK_TIMESTAMP, SIGNER_ID as creator,

RECEIVER_ID as contract, tx_hash,

rank() over (partition by contract order by BLOCK_TIMESTAMP) as rank

from near.core.fact_actions_events

where ACTION_NAME = 'DeployContract')

where rank=1),

users_projects as (

select user_address, count(*) as projects

from base

group by 1),

final as (