SELECT *, rank()over(partition by tx_signer order by block_timestamp) as rank

FROM near.core.fact_transactions

,

last_30D as (

SELECT

case when project_name = 'sweat economy' then 'sweat' else project_name end as project_name,

min(label_type) as sector,

count(DISTINCT tx_signer) as n_new_user

FROM main a

LEFT JOIN near.core.dim_address_labels b

on a.tx_RECEIVER = b.address

WHERE rank = '1'

AND block_timestamp BETWEEN '2023-12-01' AND '2023-12-31'

AND project_name is not null

GROUP by 1

),

ranking as (

SELECT project_name,

rank()over(order by n_new_user DESC) as ranking

FROM last_30D

),

last_60D as (

SELECT

case when project_name = 'sweat economy' then 'sweat' else project_name end as project_name,

count(DISTINCT tx_signer) as n_new_user

FROM main a

LEFT JOIN near.core.dim_address_labels b

on a.tx_RECEIVER = b.address

WHERE rank = '1'

AND block_timestamp BETWEEN '2023-11-01' AND '2023-11-30'

GROUP by 1

),

recurrent_users as (

SELECT