-- Get first transaction for each signer

SELECT

signers[0] as user_address,

MIN(block_timestamp) as first_tx_date

FROM solana.core.fact_transactions

WHERE block_timestamp >= DATEADD('day', -30, CURRENT_DATE())

GROUP BY signers[0]

),

weekly_new_users AS (

-- Aggregate new users by week

SELECT

DATE_TRUNC('week', first_tx_date) AS activation_week,

COUNT(DISTINCT user_address) AS new_users

FROM first_txs

GROUP BY activation_week

),

protocol_launches AS (

-- Get protocol launches based on label creation

SELECT

DATE_TRUNC('week', inserted_timestamp) as launch_week,

label,

label_type,

label_subtype

FROM solana.core.dim_labels

WHERE blockchain = 'solana'

AND inserted_timestamp >= DATEADD('day', -30, CURRENT_DATE())

AND label_type IN ('dapp', 'defi', 'dex', 'games', 'nft')

SELECT

w.activation_week,

w.new_users,

CASE

WHEN LISTAGG(DISTINCT p.label || ' (' || p.label_type || ')') IS NULL THEN 'No new protocols'

WHEN LISTAGG(DISTINCT p.label || ' (' || p.label_type || ')') = '' THEN 'No new protocols'