tx_id,

'Serum' as platform

from solana.core.fact_events

where program_id in ( select DISTINCT address

from solana.core.dim_labels

where label_type in ('defi','dex')

and label ilike '%serum%')

and date >= '2022-11-01')

,

t2 as ( select date(block_timestamp) as date,

tx_id,

'Open_book' as platform

from solana.core.fact_events

where program_id = 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX'

and date >= CURRENT_DATE - 10)

select count(DISTINCT a.tx_id) as N_TX,count(DISTINCT tx_from) as N_user,sum(amount) as solana_amount

from t2 a join solana.core.fact_transfers b on a.date = b.block_timestamp::date and a.tx_id = b.tx_id

where mint = 'So11111111111111111111111111111111111111112'

and block_timestamp::date >= '2022-11-01'