list1 as (

select tx_from as user,

min (a.block_timestamp)::date as min_date

from solana.core.fact_events a

join solana.core.fact_transfers b

on a.tx_id = b.tx_id

where succeeded = true

and program_id = 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX' --openbook

group by 1

),

list2 as (

select min_date as day,

count (distinct user) as new_users

from list1

group by 1

select

day "Date",

count (distinct a.tx_id) "TX Number",

count (distinct tx_from) "Users",

new_users "New Users",

sum("TX Number") over (order by "Date") "Comulative TX Number",

sum("Users") over (order by "Date") "Comulative Users",

sum("New Users") over (order by "Date") "Comulative New Users"

from solana.core.fact_events a

join list2

on a.block_timestamp::date = list2.day

join solana.core.fact_transfers b

on b.tx_id = a.tx_id

where program_id = 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX' --openbook

and succeeded = true

and "Date" >= '2022-11-01'

group by 1, 4