date_trunc('day', block_timestamp) as day,

tx_id,

case

when instructions[0]['programId']='srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX' then 'Openbook'

else initcap(l.label)

end as dapp_name

from

solana.core.fact_transactions t left join solana.core.dim_labels l on t.instructions[0]['programId']=l.address and l.label_type in ('dapp', 'dex')

where

SUCCEEDED

and dapp_name is not null

-- and instructions[0]['programId']='srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX'

and block_timestamp > CURRENT_DATE-30

select

day,

dapp_name,

count(distinct tx_id) as tx_cnt

from

events

group by 1, 2