SELECT

address,

label_type,

label_subtype

FROM avalanche.core.dim_labels

WHERE label_subtype LIKE '%contract' -- includes general_contract, token_contract and swap contract

),

tx_daily AS (

SELECT

date(block_timestamp) AS date,

contract_address AS contract

FROM avalanche.core.fact_event_logs

WHERE tx_status = 'SUCCESS'

),

defi AS (

SELECT

t.date AS date,

COUNT(DISTINCT t.contract) AS contract

FROM tx_daily t

JOIN contracts c ON t.contract = c.address

WHERE c.label_type = 'defi'

GROUP BY 1

),

dex AS (

SELECT

t.date AS date,

COUNT(DISTINCT t.contract) AS contract

FROM tx_daily t

JOIN contracts c ON t.contract = c.address

WHERE c.label_type = 'dex'

GROUP BY 1

),