SELECT *

FROM avalanche.core.dim_labels

WHERE label_type = 'dex'

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

),

tx_daily AS (

SELECT

date(block_timestamp) AS date,

tx_hash AS tx,

contract_address AS contract

FROM avalanche.core.fact_event_logs

WHERE tx_status = 'SUCCESS'

),

joe AS (

SELECT

t.date AS date,

COUNT(DISTINCT t.tx) AS tx

FROM tx_daily t

JOIN dexs d ON t.contract = d.address

WHERE d.project_name = 'trader joe'

GROUP BY 1

),

pangolin AS (

SELECT

t.date AS date,

COUNT(DISTINCT t.tx) AS tx

FROM tx_daily t

JOIN dexs d ON t.contract = d.address

WHERE d.project_name = 'pangolin'

GROUP BY 1

),