With all_txs AS (

SELECT

block_timestamp,

signers[0] AS user_address

FROM

solana.core.fact_events

WHERE

succeeded

AND fact_events.program_id = 'C1onEW2kPetmHmwe74YC1ESx3LnFEpVau6g2pg4fHycr'

AND block_timestamp::date >= '2024-01-01'

),

first_txs AS (

SELECT

user_address,

min(block_timestamp) AS first_timestamp

FROM

solana.core.fact_events

JOIN ( SELECT DISTINCT user_address

FROM all_txs ) users

ON fact_events.signers[0] = users.user_address

WHERE

succeeded

AND fact_events.program_id = 'C1onEW2kPetmHmwe74YC1ESx3LnFEpVau6g2pg4fHycr'

GROUP BY

1

),

aggregated AS (

SELECT

date_trunc('day', block_timestamp) AS date,

iff(first_timestamp :: date = block_timestamp :: date, 'New', 'Returning') AS category,

COUNT(DISTINCT user_address) AS users

FROM

all_txs