-- forked from saeedmzn / [New Users on NEAR (2024)] Users Distribution by protocol type @ https://flipsidecrypto.xyz/saeedmzn/q/3GeJ-maEFA_K/new-users-on-near-2024-users-distribution-by-protocol-type
with new_users as (
select TX_SIGNER ,
min (BLOCK_TIMESTAMP) min_date
from near.core.fact_transactions
group by 1 having min_date::date >= CURRENT_DATE - 30
),
labels as (
select ADDRESS ,
PROJECT_NAME,
LABEL_TYPE
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,
PROJECT_NAME
from near.core.fact_transactions join new_users using (TX_SIGNER)
left join labels on ADDRESS = TX_RECEIVER
)
select
LABEL_TYPE,
count (DISTINCT TX_SIGNER) num_new_users ,
count (DISTINCT TX_HASH) num_transactions
from transactions
group by 1