WITH list as (

SELECT label, address

FROM solana.core.dim_labels

WHERE label_type = 'dex'

), txs as (

SELECT a.label as platform, block_timestamp::date as date, COUNT(DISTINCT b.tx_id) as tx_count

FROM list as a JOIN solana.core.fact_events as b ON a.address = b.program_id

WHERE block_timestamp > CURRENT_DATE - 30

GROUP BY platform, date

SELECT date,

CASE WHEN platform = 'saber' THEN tx_count ELSE 0 END as "Saber",

CASE WHEN platform = 'orca' THEN tx_count ELSE 0 END as "Orca",

CASE WHEN platform = 'mango markets' THEN tx_count ELSE 0 END as "Mango Markets",

CASE WHEN platform = 'mercurial' THEN tx_count ELSE 0 END as "Mercurial",

CASE WHEN platform = 'serum' THEN tx_count ELSE 0 END as "Serum",

CASE WHEN platform = 'raydium' THEN tx_count ELSE 0 END as "Raydium",

CASE WHEN platform = 'zeta' THEN tx_count ELSE 0 END as "Zeta",

CASE WHEN platform = 'aldrin' THEN tx_count ELSE 0 END as "Aldrin",

CASE WHEN platform = 'jupiter' THEN tx_count ELSE 0 END as "Jupiter"

FROM txs