select

date_trunc(month, block_timestamp)::date as date ,

project_name as project ,

TX_SIGNER as user ,

count(distinct tx_hash) as transactions

from near.core.fact_transactions

join near.core.dim_address_labels on TX_RECEIVER = address

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

group by date , project , user

having transactions >=2

select

project_name ,

count (distinct TX_SIGNER) as users_count

from near.core.fact_transactions

join near.core.dim_address_labels on TX_RECEIVER = address

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

where

TX_SIGNER in (select user from base)

and

block_timestamp ilike '2023%'

group by 1 order by 2 desc limit 10