select TX_SIGNER ,

min (BLOCK_TIMESTAMP) min_date

from near.core.fact_transactions

group by 1 having min_date::date >='2024-01-01'

),

labels as (

select ADDRESS ,

PROJECT_NAME,

from near.core.dim_address_labels

),

transactions as (

select min_date ,

TX_HASH ,

TX_SIGNER ,

TX_RECEIVER,

iff( LABEL_TYPE is NULL ,'Other',LABEL_TYPE)LABEL_TYPE,

from near.core.fact_transactions join new_users using (TX_SIGNER)

left join labels on ADDRESS = TX_RECEIVER

where BLOCK_TIMESTAMP ::date >= '2024-01-01'

)

select

date_trunc(week ,min_date) weekly,

LABEL_TYPE,

count (DISTINCT TX_SIGNER) num_new_users ,

count (DISTINCT TX_HASH) num_transactions ,

sum (num_transactions) over (partition by LABEL_TYPE order by weekly) cum_transactions ,

sum (num_new_users) over (partition by LABEL_TYPE order by weekly) cum_new_users

from transactions

group by 1, 2 order by weekly