SELECT DISTINCT

from_address AS user,

min (block_timestamp) AS date1

FROM avalanche.core.fact_transactions t JOIN avalanche.core.dim_labels l

ON t.to_address = l.address

WHERE label_type = 'defi'

-- AND block_timestamp::date >= current_date - 30

GROUP BY 1

),

act_users AS (

SELECT DISTINCT

from_address,

COUNT (DISTINCT tx_hash) AS tx

FROM avalanche.core.fact_transactions t JOIN avalanche.core.dim_labels l

ON t.to_address = l.address

WHERE label_type = 'defi'

AND block_timestamp::date >= current_date - 30

GROUP BY 1

HAVING tx > 5

),

active_users AS (

SELECT DISTINCT

from_address AS active_user,

block_timestamp AS date2

FROM avalanche.core.fact_transactions t JOIN avalanche.core.dim_labels l

ON t.to_address = l.address

WHERE label_type = 'defi'

AND from_address IN (SELECT from_address FROM act_users)

AND block_timestamp::date >= current_date - 30

SELECT

-- date(date1::date) AS timespan,

COUNT (DISTINCT user) AS new_users,

COUNT (DISTINCT active_user) AS active_users

FROM new_users n JOIN active_users a ON n.date1::date = a.date2::date