-- forked from 04 - new users @ https://flipsidecrypto.xyz/edit/queries/ddc9df6a-ff40-48ab-99e9-55ec3f3760a0

-- forked from 03 - new user @ https://flipsidecrypto.xyz/edit/queries/39e84d63-6dc8-429e-a0f1-67b2ee5ab8e7

-- forked from 01 - chart @ https://flipsidecrypto.xyz/edit/queries/5db0c3a2-37c3-4de0-8b3b-8e4131f5a395

new_near AS

SELECT

min( a.block_timestamp ) AS "min date"

, a.tx_signer AS "new user"

FROM

near.core.fact_transactions a

LEFT JOIN

near.core.dim_address_labels b

ON a.tx_receiver = b.address

WHERE

b.project_name IS NOT NULL

AND a.tx_signer != a.tx_receiver

GROUP BY 2

ORDER BY 1 DESC

,

monthly_new_near AS

SELECT

date_trunc( 'month' , "min date" ) AS "date"

, count( DISTINCT "new user" ) AS "active users"

FROM

new_near

WHERE

"date" > dateadd( 'month', -{{month}} , current_date )