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 date,count(DISTINCT tx_from) as N_user,sum(N_user) over (order by date asc) as Cum_user,

count(DISTINCT a.tx_id) as N_TX,sum(N_TX) over (order by date asc) as Cum_tx,

sum(amount) as Solana_amount,sum(Solana_amount) over (order by date asc) as Cum_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'

group by date

order by date