SELECT

DISTINCT ADDRESS as addy,

ADDRESS_NAME as app

FROM solana.core.dim_labels

WHERE label_type IN ('dex', 'defi'))

, data1 as (

SELECT 'Defi transactions' as type, 'Defi intrecrors' as type2,

count(DISTINCT tx_id) as txs,

count(DISTINCT INSTRUCTION:accounts[1]) as users

FROM solana.core.fact_events

JOIN defi on program_id = addy

where BLOCK_TIMESTAMP::date >= '2022-11-01'

and BLOCK_TIMESTAMP::date < '2022-11-08'

GROUP BY 1,2)

select *

from data1

union

SELECT

'Other transactions' as type, 'Other wallets' as type2,

count(DISTINCT tx_id) as txs,

count(DISTINCT INSTRUCTION:accounts[1]) as users

FROM solana.core.fact_events

where BLOCK_TIMESTAMP::date >= '2022-11-01'

and BLOCK_TIMESTAMP::date < '2022-11-08'

and program_id not in (SELECT addy from defi)

GROUP BY 1