SELECT

block_timestamp::date AS date,

from_address AS wallet,

INITCAP(label_type) AS label_category

FROM avalanche.core.fact_event_logs

JOIN avalanche.core.dim_labels

ON address = contract_address

JOIN avalanche.core.fact_transactions USING (tx_hash)

WHERE tx_status = 'SUCCESS'

AND label_type IS NOT NULL

AND INITCAP(label_type) != 'Token'

),

first_interaction AS (

SELECT

from_address,

MIN(block_timestamp)::date AS first_date

FROM avalanche.core.fact_transactions

GROUP BY from_address

),

weekly_stats AS (

SELECT

DATE_TRUNC('week', first_date) AS date,

label_category,

COUNT(DISTINCT wallet) AS unique_wallets

FROM interactions

JOIN first_interaction

ON interactions.wallet = first_interaction.from_address

GROUP BY 1, 2

SELECT

date,

label_category,

unique_wallets

FROM weekly_stats

WHERE date >= '2021-02-01'